Basic command

Download at local (Option 1)

Download from:
https://www.postgresql.org/download/

Download at docker (Option 2)

Using docker-compose.yml


services:
  db-psql:
    image: postgres:16-alpine
    restart: always
    environment:
      POSTGRES_DB: test_db
      POSTGRES_USER: db_user
      POSTGRES_PASSWORD: 123
    ports:
      - "9877:5432" 
    volumes:
      - ./db_data:/var/lib/postgresql/data 
volumes:
  db_data:


docker compose up -d


docker exec -it <container_id> /bin/bash
 
# Inside docker 
psql -h localhost -U db_user -d test_db

For outside (Using 9877 PORT to prevent port conflicts to local PSQL):


psql -h localhost -U db_user -d test_db -p 9877

Connect to SQL


psql -U <user_name>
psql -U postgres

Basic command

- List out all current Database


\l
 
       Name       |  Owner   | Encoding |                 Collate                 |                  Ctype                  |   Access privileges
------------------+----------+----------+-----------------------------------------+-----------------------------------------+-----------------------
 sad              | postgres | UTF8     | Chinese (Traditional)_Hong Kong SAR.950 | Chinese (Traditional)_Hong Kong SAR.950 |
 cry              | postgres | UTF8     | Chinese (Traditional)_Hong Kong SAR.950 | Chinese (Traditional)_Hong Kong SAR.950 |
 abc              | postgres | UTF8     | Chinese (Traditional)_Hong Kong SAR.950 | Chinese (Traditional)_Hong Kong SAR.950 |

- Connect to target Database


\c <db_name>
\c sad
 
You are now connected to database "sad" as user "postgres".

- List out all Tables


\d
 
                       List of relations
 Schema |              Name              |   Type   |  Owner
--------+--------------------------------+----------+----------
 public | apple                          | table    | postgres
 public | apple_id_seq                   | sequence | postgres
 public | counter_devices                | table    | postgres
 public | counter_devices_id_seq         | sequence | postgres

More details with \d+


\d+
 
                                           List of relations
 Schema |              Name              |   Type   |  Owner   | Persistence |    Size    | Description
--------+--------------------------------+----------+----------+-------------+------------+-------------
 public | apple                          | table    | postgres | permanent   | 8192 bytes |
 public | apple_id_seq                   | sequence | postgres | permanent   | 8192 bytes |
 public | counter_devices                | table    | postgres | permanent   | 8192 bytes |

- List out tables details


\d <table_name>
\d apple
 
                                         Table "public.apple"
     Column     |           Type           | Collation | Nullable |              Default
----------------+--------------------------+-----------+----------+-----------------------------------
 id             | integer                  |           | not null | nextval('apple_id_seq'::regclass)
 name1          | character varying(255)   |           |          |
 level1         | character varying(255)   |           |          |

More details with \d+


\d+ <table_name>
\d+ apple
 
                                                             Table "public.apple"
     Column     |           Type           | Collation | Nullable |              Default              | Storage  | Stats target | Description
----------------+--------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id             | integer                  |           | not null | nextval('apple_id_seq'::regclass) | plain    |              |
 name1          | character varying(255)   |           |          |                                   | extended |              |
 level1         | character varying(255)   |           |          |                                   | extended |              |

Basic Utils

Create User


CREATE USER <your-username-here> WITH PASSWORD 'your-password-here' SUPERUSER;
CREATE USER peter WITH PASSWORD '123' SUPERUSER;
 
# With Login 
ALTER ROLE <your-username-here> WITH LOGIN
ALTER ROLE peter WITH LOGIN