1. Introduction

source: PostgreSQL 18.3 Documentation

Installation: Install PostgreSQL using the command sudo apt install postgresql.

Database Roles: Roles are database access permission. It defines a user or a group of users authorized to use database. Before creating or reading any databse, we first need to create a role. Create a role with the following command.

sudo -u postgres createuser --superuser $USER

Create Database: Next, we create a database with name $USER using the following command.

sudo -u postgres createdb $USER

Create History: Create a file at home to save the history using touch .psql_history.

Connect to the Server: Once the setup is done, connect to the server using psql. The command takes the default role and the database. Enter \q to exit the database.

2. SQL Commands

2.1 Create Table

We create table in two steps. We first define the schema for the table as illustrated below. After creating the table schema we insert record into the table.

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

We create another table for cities.

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

To delete a table, use DROP TABLE tablename;.

2.2 Insert Values

To insert rows into a table, we use insert command as below.

insert into weather values ('San Francisco', 46, 50, 0.25, '1994-11-27');
insert into cities values ('San Francisco', '(-194.0, 53.0)');

3. Python Client

source: Using psycopg2 with PostgreSQL

The standard and most common library to connect to PostgreSQL is psycopg2. Install it using pip install psycop2-binary.

To connect to the database run the following command.

import psycopg2 as sql

conn = sql.connect(database='dbname', user='username')

cursor = conn.cursor()

cursor.execute("select * from weather;")

print(cursor.fetchall())

[(‘San Francisco’, 46, 50, 0.25, datetime.date(1994, 11, 27))]