PostgreSQL

PostgreSQL is a free, powerful SQL database which is frequently used with Python

How to connect to postgres

sudo -i -u postgres

run psql

postgres@rag-laptop:~$ psql
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

list databases

postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 gis       | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 suppliers | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

connect to a database

postgres=# c suppliers
You are now connected to database "suppliers" as user "postgres".

create a database

postgres=# CREATE DATABASE jml;
CREATE DATABASE
postgres=# l
                                   List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 gis         | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 jml         | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |

create a table

postgres=# c jml;
You are now connected to database "jml" as user "postgres".
jml=# CREATE TABLE PRODUCTS (id serial, product varchar(40), price money);
CREATE TABLE
jml=# 

insert into table

jml=# INSERT INTO PRODUCTS (product,price) VALUES('jug',29.99);
INSERT 0 1
jml=# INSERT INTO PRODUCTS (product,price) VALUES('pot',9.99) RETURNING *;
 id | product | price 
----+---------+-------
  2 | pot     | £9.99
(1 row)

INSERT 0 1

select from a table

jml=# SELECT product FROM products WHERE price < '29.99';
 product 
---------
 pot
(1 row)

store ip addresses in a table

jml=# CREATE TABLE inet_test (  
jml(#     address INET
jml(# );
CREATE TABLE

#### table created

jml=# d
               List of relations
 Schema |      Name       |   Type   |  Owner   
--------+-----------------+----------+----------
 public | inet_test       | table    | postgres
 public | products        | table    | postgres
 public | products_id_seq | sequence | postgres
(3 rows)

jml=# INSERT INTO inet_test (address) VALUES ('192.168.1.0/24'); 
INSERT 0 1
jml=# INSERT INTO inet_test (address) VALUES ('172.16.11.0/24') RETURNING *; 
    address     
----------------
 172.16.11.0/24
(1 row)

Use CIDR notation

jml=# CREATE TABLE cidr_test (  
jml(#     address CIDR
jml(# );

INSERT INTO cidr_test (address) VALUES ('192.168.10/24');  
INSERT INTO cidr_test (address) VALUES ('192.168.10');  
INSERT INTO cidr_test (address) VALUES ('192.168.100.128/25');  
INSERT INTO cidr_test (address) VALUES ('192.168.100.128'); 

jml=# INSERT INTO cidr_test (address) VALUES ('192.168.10/24');  
INSERT 0 1
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.10');  
INSERT 0 1
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.100.128/25');  
INSERT 0 1
jml=# INSERT INTO cidr_test (address) VALUES ('192.168.100.128');
INSERT 0 1
jml=# select * from cidr_test;
      address       
--------------------
 192.168.10.0/24
 192.168.10.0/24
 192.168.100.128/25
 192.168.100.128/32
(4 rows)

Create postgres user for Python Script

Syntax = 

CREATE USER user3 WITH
LOGIN
SUPERUSER
CREATEDB
CREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
VALID UNTIL '2025-04-03T11:50:38+05:30' 
PASSWORD 'password3';

Full commands to run to set up user on Raspberry Pi (aka 'The Server') :

postgres=# pi@pi4:~$ sudo -i -u postgres
postgres-# postgres@pi4:~$ psql
postgres-# psql (11.11 (Raspbian 11.11-0+deb10u1))
postgres-# Type "help" for help.
postgres-#
postgres=# CREATE USER user3 WITH
postgres-# LOGIN
postgres-# SUPERUSER
postgres-# CREATEDB
postgres-# CREATEROLE
postgres-# INHERIT
postgres-# NOREPLICATION
postgres-# CONNECTION LIMIT -1
postgres-# VALID UNTIL '2025-04-03T11:50:38+05:30'
postgres-# PASSWORD 'password3';

Check the new user has been added

\du

Install psycopg2 to connect to postgres using Python