Postgresql tips and tricks

Cheatsheet #

|===================================================|=======================================================| | What | How | |===================================================|=======================================================|

0. Setup #

Docker compose #

I use postgres as a docker container:

docker-compose.yml:

version: "3.4"

services:
  postgres:
    image: postgres
    restart: unless-stopped
    volumes:
      - postgres:/var/lib/postgresql/data
      - ./init-database.sh:/docker-entrypoint-initdb.d/init-database.sh
      - ./dumps:/dumps
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - 5432:5432

volumes:
  postgres:

Start psql #

Use docker exec -it <container id or name> psql -U postgres.

Docker compose will add a name automatically to your container:

docker exec -it project_name_postgres_1 psql -U postgres"

1. Basics #

List databases #

Just \l:

postgres=# \l

Use database #

Use \c <database_name>

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

List tables #

Use \dt:

project_development=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner
--------+----------------------+-------+----------
 public | files                | table | postgres
 public | form_types           | table | postgres
 public | knex_migrations      | table | postgres
 public | knex_migrations_lock | table | postgres
 public | requests             | table | postgres
 public | users                | table | postgres
(6 rows)

Table schema #

Psql command: \d+ tablename:

project_development=# \d+ users
                                                           Table "public.users"
   Column   |           Type           | Collation | Nullable |              Default              | Storage  | Stats target | Description
------------+--------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id         | integer                  |           | not null | nextval('users_id_seq'::regclass) | plain    |              |
 username   | character varying(255)   |           | not null |                                   | extended |              |
 email      | character varying(255)   |           | not null |                                   | extended |              |
 created_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP                 | plain    |              |
 updated_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP                 | plain    |              |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_unique" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "requests" CONSTRAINT "tasks_created_by_foreign" FOREIGN KEY (created_by) REFERENCES users(id)
Access method: heap

Export/import table as CSV #

Command: copy <table name> <column names> [from|to] '<full file path to CSV file>' DELIMITER ',' CSV HEADER;

project_development=# copy users (username, email) to '/db-dumps/users.csv' DELIMITER ',' CSV HEADER;

Query #

Specific features #

Cast #

You can cast a value:

select cast (now() as date);

But the :: operator do the same:

select now()::date

On conflict #

insert into users (user_handle, first_name, last_name, email) values (uuid_generate_v4(), 'Lucie', 'Jones', 'Lucie-Jones@gmail.com') on conflict do nothing: