Cheatsheet - PostgreSQL

Connecting & psql Meta-Commands

Once you're in the psql interactive terminal, you can use these helpful "meta-commands," which are preceded by a backslash.

Connect to a database: \c <database_name>
List all databases: \l or \list
List all tables in the current database: \dt
Describe a table (columns, types, etc.): \d <table_name>
List all users and their roles: \du
Get help on a specific SQL command: \h <SQL_COMMAND> (e.g., \h CREATE TABLE)
Quit psql: \q
Toggle expanded display for wide results: \x

These commands are used to define and manage the structure of your database.

CREATE TABLE <table_name> (
   column1_name data_type constraints,
   column2_name data_type,
   ...
);

CREATE TABLE users (
   id SERIAL PRIMARY KEY,
   username VARCHAR(50) NOT NULL,
   email VARCHAR(100) UNIQUE
);

Drop a Table: DROP TABLE <table_name>;
Alter a Table:
Add a column: ALTER TABLE <table_name> ADD COLUMN <new_column_name> data_type;
Drop a column: ALTER TABLE <table_name> DROP COLUMN <column_name>;
Rename a column: ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>;
Change a column's data type: ALTER TABLE <table_name> ALTER COLUMN <column_name> TYPE new_data_type;

Use these commands to interact with the data within your tables.

INSERT INTO <table_name> (column1, column2) VALUES (value1, value2);

INSERT INTO <table_name> (column1, column2) VALUES
   (value1, value2),
   (value3, value4),
   (value5, value6);

UPDATE <table_name> SET column1 = new_value WHERE condition;

DELETE FROM <table_name> WHERE condition;

These commands are used to retrieve data from your database.

Select All Columns: SELECT * FROM <table_name>;
Select Specific Columns: SELECT column1, column2 FROM <table_name>;
Filtering with WHERE: SELECT * FROM <table_name> WHERE condition;
Sorting with ORDER BY: SELECT * FROM <table_name> ORDER BY column_name ASC|DESC;
Limiting Results: SELECT * FROM <table_name> LIMIT 10;
Unique Values: SELECT DISTINCT column_name FROM <table_name>;
Aggregate Functions: AVG(), COUNT(), MAX(), MIN(), SUM()
Grouping Data: SELECT column1, COUNT(*) FROM <table_name> GROUP BY column1;
Filtering Groups: SELECT column1, COUNT(*) FROM <table_name> GROUP BY column1 HAVING COUNT(*) > 5;

Combine rows from two or more tables based on a related column.

Inner Join: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id;
Left Join: SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_id;
Right Join: SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.table1_id;
Full Outer Join: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.table1_id;

PostgreSQL supports a wide range of data types.

Numeric: INTEGER, BIGINT, NUMERIC(precision, scale), REAL, DOUBLE PRECISION, SERIAL
Character: VARCHAR(n), CHAR(n), TEXT
Date/Time: DATE, TIME, TIMESTAMP, INTERVAL
Boolean: BOOLEAN
JSON: JSON, JSONB
UUID: UUID

These commands are used to manage users and their access to database objects.

Create a User: CREATE USER <username> WITH PASSWORD '<password>';
Drop a User: DROP USER <username>;
Grant Privileges: GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;
Grant specific permissions on a table: GRANT SELECT, INSERT, UPDATE, DELETE ON <table_name> TO <username>;
Grant all permissions on all tables in a schema: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
Revoke Privileges: REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM <username>;

Use these command-line utilities to back up and restore your databases.

pg_dump -U <username> -W -F c -f <backup_file.dump> <database_name>

pg_restore -U <username> -W -d <database_name> <backup_file.dump>

$ postgres -D /path/to/data

# start in the background
$ postgres -D /usr/local/pgsql/data >logfile 2>&1 &

pg_hba.conf controls client authentication (HBA stands for host-based authentication.)

$ find / -name "pg_hba.conf"

pg_hba.conf file format: a set of records, one per line.

Use pg_isready