Reference Guide to PostgreSQL Commands and Syntax

PostgreSQL cheat sheet

This PostgreSQL cheat sheet covers everything from basic connections and SQL queries to advanced features like window functions and transactions. It includes commands for database management, DDL/DML operations, joins, constraints, user permissions, data types, and psql meta-commands.

Published:: 2025-10-13
Updated:: 2025-10-27
Reading time:: 04:20
Author:: Jonas Jared Jacek
Tags:: cheat sheet
License:: CC BY-SA 4.0
Permalink:: PostgreSQL cheat sheet

Introduction #

This cheat sheet provides you with a structured overview of the most important PostgreSQL (Postgres) commands. PostgreSQL is an open-source relational database management system (RDBMS) that uses the Structured Query Language (SQL) for interacting with databases.

Use this as a reference for basic queries, Data Definition Language (DDL), Data Manipulation Language (DML), connection options, user roles, and administrative tasks.

Connecting to PostgreSQL #

This section covers how to establish a connection to a PostgreSQL database using the psql command-line interface.

Option	Example	Description
`psql -U username -d dbname`	`psql -U john -d inventory`	Connect to the `inventory` database as user `john`.
`psql -h host -p port`	`psql -h localhost -p 5432`	Specify host and port when connecting.
`psql -W`	`psql -W -U john`	Prompt for password before connecting.
`\c dbname`	`\c sales`	Connect to the `sales` database within `psql`.
`\conninfo`	`\conninfo`	Display information about the current database connection.
`\l`	`\l`	List all databases in `psql`.
`\q`	`\q`	Quit the `psql` session.

Basic SQL commands #

These are the fundamental SQL statements used to query and retrieve data from tables.

Option	Example	Description
`SELECT`	`SELECT * FROM employees;`	Retrieve all rows from the `employees` table.
`WHERE`	`SELECT * FROM employees WHERE department = 'HR';`	Filter rows based on a condition.
`BETWEEN`	`SELECT * FROM products WHERE price BETWEEN 10 AND 20;`	Filter rows within a specified range (inclusive).
`LIKE/ILIKE`	`SELECT * FROM users WHERE name LIKE 'A%';`	Filter using pattern matching (`ILIKE` is case-insensitive).
`ORDER BY`	`SELECT name FROM employees ORDER BY name;`	Sort result by one or more columns.
`LIMIT`	`SELECT * FROM employees LIMIT 5;`	Limit result to a specific number of rows.
`DISTINCT`	`SELECT DISTINCT department FROM employees;`	Return unique values only.

Data Definition Language (DDL) #

DDL statements are used to define, modify, and delete database structures like databases, tables, and indexes.

Databases #

These commands manage the creation and deletion of entire databases.

Option	Example	Description
`CREATE DATABASE`	`CREATE DATABASE company;`	Create a new database.
`DROP DATABASE`	`DROP DATABASE company;`	Delete a database permanently.

Tables #

Table commands define the structure of your data, allowing you to create, alter, and drop tables.

Option	Example	Description
`CREATE TABLE`	`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);`	Create a new table with specified columns.
`DROP TABLE`	`DROP TABLE users;`	Delete an existing table.
`ALTER TABLE`	`ALTER TABLE users ADD COLUMN email TEXT;`	Modify the structure of an existing table.
`RENAME TABLE`	`ALTER TABLE employees RENAME TO staff;`	Rename an existing table.
`TRUNCATE TABLE`	`TRUNCATE TABLE employees;`	Remove all rows from a table without deleting it.

Indexes #

Indexes are used to improve the speed of data retrieval operations on a table.

Option	Example	Description
`CREATE INDEX`	`CREATE INDEX idx_name ON users(name);`	Create an index on the `name` column.
`UNIQUE INDEX`	`CREATE UNIQUE INDEX idx_email ON employees(email);`	Ensure all indexed values are unique.
`REINDEX`	`REINDEX TABLE employees;`	Rebuild corrupted or outdated indexes.
`DROP INDEX`	`DROP INDEX idx_name;`	Delete an index.

Constraints #

Constraints enforce data integrity rules at the database level, ensuring accuracy and reliability.

Option	Example	Description
`PRIMARY KEY`	`id SERIAL PRIMARY KEY`	Uniquely identify each record in a table.
`FOREIGN KEY`	`FOREIGN KEY (dept_id) REFERENCES departments(id)`	Establish a relationship between two tables.
`UNIQUE`	`email TEXT UNIQUE`	Ensure all values in a column are unique.
`NOT NULL`	`name TEXT NOT NULL`	Ensure the column cannot contain NULL values.
`CHECK`	`salary NUMERIC CHECK (salary > 0)`	Enforce a condition for valid data.
`DEFAULT`	`joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP`	Set a default value if none is provided.

Data Manipulation Language (DML) #

DML commands are used for managing data within table objects, including inserting, updating, and deleting records.

Option	Example	Description
`INSERT`	`INSERT INTO users (name) VALUES ('Alice');`	Add a new row to the table.
`UPDATE`	`UPDATE users SET name = 'Bob' WHERE id = 1;`	Modify existing rows.
`DELETE`	`DELETE FROM users WHERE id = 1;`	Remove rows from a table matching a condition.
`RETURNING`	`INSERT INTO users (name) VALUES ('Dan') RETURNING id;`	Return values after insert, update, or delete.
`ON CONFLICT`	`INSERT INTO users (id, email) VALUES (1, 'a@b.com') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;`	Insert or update on conflict (upsert).

Joins #

Joins combine rows from two or more tables based on a related column between them.

Option	Example	Description
`INNER JOIN`	`SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;`	Return only matching rows.
`LEFT JOIN`	`SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;`	Return all rows from the left table and matched rows from the right.
`RIGHT JOIN`	`SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;`	Return all rows from the right table and matched rows from the left.
`FULL JOIN`	`SELECT * FROM orders FULL JOIN customers ON orders.customer_id = customers.id;`	Return all matched and unmatched rows from both tables.
`CROSS JOIN`	`SELECT e.name, d.name FROM employees e CROSS JOIN departments d;`	Return Cartesian product of two tables.
`SELF JOIN`	`SELECT a.name, b.manager FROM employees a JOIN employees b ON a.id = b.manager_id;`	Join a table to itself.

Aggregation and grouping #

Aggregate functions perform a calculation on a set of rows and return a single value, often used with GROUP BY.

Option	Example	Description
`COUNT()`	`SELECT COUNT(*) FROM users;`	Count number of rows.
`SUM()`	`SELECT SUM(price) FROM products;`	Calculate total sum.
`AVG()`	`SELECT AVG(age) FROM users;`	Calculate average value.
`MIN()`	`SELECT MIN(salary) FROM employees;`	Return the smallest value.
`MAX()`	`SELECT MAX(salary) FROM employees;`	Return the largest value.
`STRING_AGG()`	`SELECT department, STRING_AGG(name, ', ') FROM employees GROUP BY department;`	Concatenate strings from a group into a single string.
`GROUP BY`	`SELECT department, COUNT(*) FROM employees GROUP BY department;`	Group results by column and apply aggregation.
`HAVING`	`SELECT department, COUNT() FROM employees GROUP BY department HAVING COUNT() > 5;`	Filter grouped results.

Common table expressions (CTEs) #

CTEs create temporary result sets that can be referenced within a larger query, improving readability and enabling recursion.

Option	Example	Description
`WITH`	`WITH regional_sales AS (SELECT region, SUM(amount) FROM orders GROUP BY region) SELECT * FROM regional_sales;`	Define a CTE for use in the main query.

Window functions #

Window functions perform calculations across a set of table rows that are somehow related to the current row.

Option	Example	Description
`OVER()`	`SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;`	Define the window for the function to operate on.
`ROW_NUMBER()`	`SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)`	Assign a unique sequential integer to rows within a partition.
`LAG() / LEAD()`	`SELECT sales, LAG(sales) OVER (ORDER BY date) FROM daily_sales;`	Access data from a previous or subsequent row.

Transactions #

Transactions group a set of database tasks into a single unit of work that is either fully completed or fully rolled back.

Option	Example	Description
`BEGIN`	`BEGIN;`	Start a transaction block.
`COMMIT`	`COMMIT;`	Save all changes made during the transaction.
`ROLLBACK`	`ROLLBACK;`	Undo all changes since the transaction began.
`SAVEPOINT`	`SAVEPOINT my_save;`	Set a savepoint within a transaction to allow partial rollback.
`ROLLBACK TO SAVEPOINT`	`ROLLBACK TO SAVEPOINT my_save;`	Roll back to a specific savepoint.

psql Meta-commands #

These are special commands for the psql client that provide information about the database and control the client’s behavior.

Option	Example	Description
`\dt`	`\dt`	List all tables in the current database.
`\d tablename`	`\d users`	Show schema/structure of a table.
`\du`	`\du`	List all roles.
`\dn`	`\dn`	List all schemas.
`\x`	`\x`	Toggle expanded output.
`\timing`	`\timing`	Toggle timing of queries.
`\i filename`	`\i my_script.sql`	Execute SQL commands from a file.
`\?`	`\?`	Show help for psql meta-commands.

User roles and permissions #

These commands manage database access control, defining users, roles, and their privileges on various objects.

Option	Example	Description
`CREATE USER`	`CREATE USER alice WITH PASSWORD 'password';`	Create a new database user.
`GRANT`	`GRANT SELECT ON employees TO alice;`	Grant privileges to a user.
`REVOKE`	`REVOKE INSERT ON employees FROM alice;`	Remove privileges from a user.
`ALTER ROLE`	`ALTER ROLE alice CREATEDB;`	Modify a user’s attributes.
`ALTER USER`	`ALTER USER john WITH SUPERUSER;`	Modify an existing user’s privileges.
`DROP USER`	`DROP USER john;`	Delete a user account.

Import and export #

These utilities are essential for moving data into and out of your PostgreSQL database.

Option	Example	Description
`\copy`	`\copy users FROM 'data.csv' CSV HEADER;`	Import data from a CSV file (client-side).
`\copy`	`\copy users TO 'output.csv' CSV HEADER;`	Export table to a CSV file (client-side).
`COPY`	`COPY users FROM '/path/data.csv' CSV HEADER;`	Import data from a CSV file (server-side, superuser).
`pg_dump`	`pg_dump -U john -d inventory > dump.sql`	Export database to a SQL file.
`psql`	`psql -U john -d inventory < dump.sql`	Restore a database from a dump file.

Data types #

PostgreSQL supports a rich set of data types to store various kinds of information.

Type Category	Example Data Type	Description
Numeric	`INTEGER`, `NUMERIC`, `SERIAL`, `BIGINT`	Store numbers, with or without decimals.
Character	`CHAR(n)`, `VARCHAR(n)`, `TEXT`	Store fixed or variable-length text strings.
Date/Time	`DATE`, `TIME`, `TIMESTAMP`, `INTERVAL`	Store date and time information.
Boolean	`BOOLEAN`	Store logical TRUE or FALSE values.
JSON/XML	`JSON`, `JSONB`, `XML`	Store structured or semi-structured data.
Array	`INTEGER[]`, `TEXT[]`	Store arrays of elements of the same type.
UUID	`UUID`	Store Universally Unique Identifiers.
Spatial	`GEOMETRY`, `GEOGRAPHY`	Store geometric and geographic data (via PostGIS).
Full-Text Search	`TSVECTOR`, `TSQUERY`	Store and query optimized text for full-text search.

Getting help #

This section covers commands to access documentation and help directly within the psql command-line interface.

Option	Example	Description
`\h`	`\h`	Get help on the syntax of SQL commands.
`\h COMMAND`	`\h DELETE`	Get detailed syntax help for a specific SQL command (e.g., DELETE).
`\?`	`\?`	List all available psql meta-commands.

FAQ's #

Most common questions and brief, easy-to-understand answers on the topic:

How do you connect to a PostgreSQL database using the command line?

Use psql -U username -d database_name to connect to a PostgreSQL database via the terminal.

What is the difference between `WHERE` and `HAVING` in SQL?

WHERE filters rows before grouping, while HAVING filters groups after aggregation.

How do you list all tables in a PostgreSQL database?

Use \dt inside psql to list all tables.

How do you check your PostgreSQL version?

Use SELECT version(); or run psql --version in the terminal.

How do you export a PostgreSQL table to a CSV file?

Use \copy tablename TO 'file.csv' CSV HEADER; inside psql.

Author

Jonas Jared Jacek (J15k)

Jonas works as project manager, web designer, and web developer since 2001. On top of that, he is a Linux system administrator with a broad interest in things related to programming, architecture, and design. See: https://www.j15k.com/

License

PostgreSQL cheat sheet by Jonas Jared Jacek is licensed under CC BY-SA 4.0.

This license requires that reusers give credit to the creator. It allows reusers to distribute, remix, adapt, and build upon the material in any medium or format, for noncommercial purposes only. To give credit, provide a link back to the original source, the author, and the license e.g. like this:

<p xmlns:cc="http://creativecommons.org/ns#" xmlns:dct="http://purl.org/dc/terms/"><a property="dct:title" rel="cc:attributionURL" href="https://www.ditig.com/postgresql-cheat-sheet">PostgreSQL cheat sheet</a> by <a rel="cc:attributionURL dct:creator" property="cc:attributionName" href="https://www.j15k.com/">Jonas Jared Jacek</a> is licensed under <a href="https://creativecommons.org/licenses/by-sa/4.0/" target="_blank" rel="license noopener noreferrer">CC BY-SA 4.0</a>.</p>

For more information see the Ditig legal page.

PostgreSQL cheat sheet

Introduction #

Connecting to PostgreSQL #

Basic SQL commands #