Howtodojo

PostgreSQL Cheatsheet

Introduction

PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its reliability and extensibility. It supports SQL (Structured Query Language) and offers a wide range of features, making it a popular choice for both small and large-scale applications.

This page contains a comprehensive cheatsheet of commonly used PostgreSQL commands and operations.

psql Command Line Basics

CommandDescriptionExample
\qQuit psql command-line utility\q
\lList all databases\l
\c database_nameConnect to a specific database\c myapp
\dtList all tables in current database\dt
\d table_nameShow details of a specific table\d users
\duList all users and roles\du
\dnList all schemas\dn
\dfList all functions\df
\dvList all views\dv
\diList all indexes\di
\eOpen text editor to edit query\e
\h commandShow help for SQL command\h CREATE TABLE
\?Show help for psql commands\?

Database Operations

CommandDescriptionExample
CREATE DATABASE name;Create a new databaseCREATE DATABASE myapp;
DROP DATABASE name;Delete an existing databaseDROP DATABASE oldapp;
ALTER DATABASE old_name RENAME TO new_name;Rename a databaseALTER DATABASE myapp RENAME TO newapp;
CREATE SCHEMA schema_name;Create a new schemaCREATE SCHEMA accounting;
DROP SCHEMA schema_name;Delete a schemaDROP SCHEMA old_schema;

Table Operations

CommandDescriptionExample
CREATE TABLE table_name (columns);Create a new tableCREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));
DROP TABLE table_name;Delete an existing tableDROP TABLE old_users;
ALTER TABLE table_name ADD COLUMN column_def;Add a new columnALTER TABLE users ADD COLUMN email VARCHAR(255);
ALTER TABLE table_name DROP COLUMN column_name;Remove a columnALTER TABLE users DROP COLUMN phone;
ALTER TABLE table_name RENAME TO new_name;Rename a tableALTER TABLE users RENAME TO customers;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;Rename a columnALTER TABLE users RENAME COLUMN name TO full_name;
TRUNCATE TABLE table_name;Delete all rows from tableTRUNCATE TABLE logs;

Data Types

Data TypeDescriptionExample
SERIALAuto-incrementing integerid SERIAL PRIMARY KEY
INTEGER / INT32-bit integerage INTEGER
BIGINT64-bit integeruser_id BIGINT
VARCHAR(n)Variable character stringname VARCHAR(100)
TEXTUnlimited textdescription TEXT
BOOLEANTrue/Falseis_active BOOLEAN
DATEDate onlybirth_date DATE
TIMESTAMPDate and timecreated_at TIMESTAMP
TIMESTAMPTZTimestamp with timezoneupdated_at TIMESTAMPTZ
DECIMAL(p,s)Fixed-point numberprice DECIMAL(10,2)
JSONJSON datametadata JSON
JSONBBinary JSON (faster)settings JSONB
UUIDUniversally unique identifierid UUID

Data Manipulation (CRUD)

INSERT Operations

CommandDescriptionExample
INSERT INTO table VALUES (...);Insert single rowINSERT INTO users VALUES (1, 'John', '[email protected]');
INSERT INTO table (columns) VALUES (...);Insert with specific columnsINSERT INTO users (name, email) VALUES ('Jane', '[email protected]');
INSERT INTO table VALUES (...), (...);Insert multiple rowsINSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT ... RETURNING *;Insert and return inserted dataINSERT INTO users (name) VALUES ('Tom') RETURNING id, name;

SELECT Operations

CommandDescriptionExample
SELECT * FROM table;Retrieve all recordsSELECT * FROM users;
SELECT columns FROM table;Select specific columnsSELECT name, email FROM users;
SELECT * FROM table WHERE condition;Filter with conditionSELECT * FROM users WHERE age > 18;
SELECT * FROM table LIMIT n;Limit number of resultsSELECT * FROM users LIMIT 10;
SELECT * FROM table OFFSET n;Skip first n rowsSELECT * FROM users OFFSET 20 LIMIT 10;

UPDATE Operations

CommandDescriptionExample
UPDATE table SET column = value;Update all rowsUPDATE users SET is_active = true;
UPDATE table SET column = value WHERE condition;Update specific rowsUPDATE users SET email = '[email protected]' WHERE id = 1;
UPDATE table SET col1 = val1, col2 = val2;Update multiple columnsUPDATE users SET name = 'John Doe', age = 30 WHERE id = 1;
UPDATE ... RETURNING *;Update and return modified dataUPDATE users SET age = 25 WHERE id = 1 RETURNING *;

DELETE Operations

CommandDescriptionExample
DELETE FROM table;Delete all rowsDELETE FROM logs;
DELETE FROM table WHERE condition;Delete specific rowsDELETE FROM users WHERE age < 18;
DELETE ... RETURNING *;Delete and return deleted dataDELETE FROM users WHERE id = 1 RETURNING *;

Querying and Filtering

CommandDescriptionExample
WHERE column = valueExact matchWHERE name = 'John'
WHERE column LIKE patternPattern matchingWHERE name LIKE 'J%'
WHERE column ILIKE patternCase-insensitive patternWHERE name ILIKE 'john%'
WHERE column IN (values)Match any in listWHERE age IN (18, 21, 25)
WHERE column BETWEEN a AND bRange matchingWHERE age BETWEEN 18 AND 65
WHERE column IS NULLCheck for NULLWHERE email IS NULL
WHERE column IS NOT NULLCheck for NOT NULLWHERE email IS NOT NULL
ORDER BY column ASC/DESCSort resultsORDER BY created_at DESC
GROUP BY columnGroup resultsGROUP BY department
HAVING conditionFilter grouped resultsHAVING COUNT(*) > 5

Joins

CommandDescriptionExample
INNER JOINReturn matching records from both tablesSELECT * FROM users u INNER JOIN orders o ON u.id = o.user_id
LEFT JOINReturn all from left table, matching from rightSELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id
RIGHT JOINReturn all from right table, matching from leftSELECT * FROM users u RIGHT JOIN orders o ON u.id = o.user_id
FULL OUTER JOINReturn all records from both tablesSELECT * FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id
CROSS JOINCartesian product of both tablesSELECT * FROM users CROSS JOIN products

Aggregate Functions

FunctionDescriptionExample
COUNT(*)Count all rowsSELECT COUNT(*) FROM users;
COUNT(column)Count non-NULL valuesSELECT COUNT(email) FROM users;
SUM(column)Sum of numeric valuesSELECT SUM(amount) FROM orders;
AVG(column)Average of numeric valuesSELECT AVG(age) FROM users;
MIN(column)Minimum valueSELECT MIN(created_at) FROM users;
MAX(column)Maximum valueSELECT MAX(price) FROM products;
STRING_AGG(column, delimiter)Concatenate stringsSELECT STRING_AGG(name, ', ') FROM users;

Indexes

CommandDescriptionExample
CREATE INDEX idx_name ON table (column);Create index on columnCREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_name ON table (column);Create unique indexCREATE UNIQUE INDEX idx_users_username ON users (username);
CREATE INDEX CONCURRENTLY idx_name ON table (column);Create index without lockingCREATE INDEX CONCURRENTLY idx_users_name ON users (name);
DROP INDEX idx_name;Remove indexDROP INDEX idx_users_email;
REINDEX INDEX idx_name;Rebuild indexREINDEX INDEX idx_users_email;

Constraints

CommandDescriptionExample
PRIMARY KEYPrimary key constraintid SERIAL PRIMARY KEY
FOREIGN KEYForeign key constraintuser_id INTEGER REFERENCES users(id)
UNIQUEUnique constraintemail VARCHAR(255) UNIQUE
NOT NULLNot null constraintname VARCHAR(100) NOT NULL
CHECKCheck constraintage INTEGER CHECK (age >= 0)
DEFAULTDefault valuecreated_at TIMESTAMP DEFAULT NOW()

User and Permission Management

CommandDescriptionExample
CREATE USER username WITH PASSWORD 'password';Create new userCREATE USER john WITH PASSWORD 'secret123';
CREATE ROLE rolename;Create new roleCREATE ROLE manager;
ALTER USER username WITH SUPERUSER;Grant superuser privilegesALTER USER john WITH SUPERUSER;
GRANT privilege ON table TO user;Grant table permissionsGRANT SELECT, INSERT ON users TO john;
REVOKE privilege ON table FROM user;Revoke table permissionsREVOKE DELETE ON users FROM john;
GRANT CONNECT ON DATABASE db TO user;Grant database accessGRANT CONNECT ON DATABASE myapp TO john;
DROP USER username;Delete userDROP USER old_user;

Useful Functions

FunctionDescriptionExample
NOW()Current timestampSELECT NOW();
CURRENT_DATECurrent dateSELECT CURRENT_DATE;
EXTRACT(field FROM date)Extract date partSELECT EXTRACT(YEAR FROM birth_date) FROM users;
COALESCE(val1, val2, ...)Return first non-null valueSELECT COALESCE(nickname, name, 'Anonymous') FROM users;
NULLIF(val1, val2)Return NULL if values equalSELECT NULLIF(age, 0) FROM users;
CASE WHEN condition THEN result ENDConditional logicSELECT CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END FROM users;
CONCAT(str1, str2, ...)Concatenate stringsSELECT CONCAT(first_name, ' ', last_name) FROM users;
LENGTH(string)String lengthSELECT LENGTH(name) FROM users;
UPPER(string)Convert to uppercaseSELECT UPPER(name) FROM users;
LOWER(string)Convert to lowercaseSELECT LOWER(email) FROM users;

Performance and Monitoring

CommandDescriptionExample
EXPLAIN query;Show query execution planEXPLAIN SELECT * FROM users WHERE age > 25;
EXPLAIN ANALYZE query;Show actual execution statisticsEXPLAIN ANALYZE SELECT * FROM users JOIN orders ON users.id = orders.user_id;
VACUUM table_name;Clean up dead rowsVACUUM users;
ANALYZE table_name;Update table statisticsANALYZE users;
REINDEX TABLE table_name;Rebuild all indexes on tableREINDEX TABLE users;

Backup and Restore

CommandDescriptionExample
pg_dump dbname > backup.sqlBackup database to SQL filepg_dump myapp > myapp_backup.sql
pg_dump -t table_name dbname > table_backup.sqlBackup single tablepg_dump -t users myapp > users_backup.sql
psql dbname < backup.sqlRestore from SQL filepsql myapp < myapp_backup.sql
pg_dumpall > all_databases.sqlBackup all databasespg_dumpall > full_backup.sql

Tips and Best Practices

  • Always use transactions for multiple related operations
  • Use EXPLAIN ANALYZE to optimize slow queries
  • Create indexes on frequently queried columns
  • Use LIMIT for large result sets to avoid memory issues
  • Regularly run VACUUM and ANALYZE for performance
  • Use SERIAL or UUID for primary keys
  • Always backup before major changes

For more detailed information, refer to the PostgreSQL Documentation.

Prev
MySQL Cheat Sheet
Next
Sed Cheat Sheet