Postgres Cheatsheet

postgres-cheatsheet.md

Pull Postgres

$ docker pull postgres

Create a directory for db persistence

$ mkdir -p [db-volume-location-on-machine]

Run the postgres container

$ docker run --rm --name [container-name] -e POSTGRES_PASSWORD=[db-password] -d -p 5432:5432 \
-v [db-volume-location-on-machine]:/var/lib/postgresql/data postgres

Connect to docker container

$ docker exec -it pg-docker psql -U postgres

Commands

\l - List all databases
\c [database] - connect to database
\dn - list schemas
\db - list tablespaces
\dt - list of relations
\dt [schema].* - list relations in schema

Keyword priority

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

URI

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

Usage

Update column NOT NULL value

ALTER TABLE table ALTER COLUMN column { SET | DROP } NOT NULL;

Update all columns' values where condition is true

UPDATE table SET column1 = value1, column2 = value2 ,... WHERE condition;

Add column to table

ALTER TABLE table ADD COLUMN column type;