320 x 50 Mobile Ad
Cheat Sheets

PostgreSQL Cheat Sheet

Transform, format, and optimize your data instantly with this free client-side tool.

CLI (psql) & Access

psql -U username -d dbname

Connect to database as user

psql -h host -p port -U user -d db

Connect with host and port

pg_dump -U user dbname > backup.sql

Dump database to a file

pg_restore -U user -d dbname backup.tar

Restore database from archive

psql Commands

\l

List all databases

\c dbname

Connect to different database

\dt

List all tables

\d table_name

Show table schema/structure

\du

List roles / users

\df

List functions

\dv

List views

\q

Quit psql

\x

Toggle expanded display mode (vertical output)

\timing

Toggle query execution time info

Databases & Schemas

CREATE DATABASE db_name;

Create new database

DROP DATABASE db_name;

Delete database

CREATE SCHEMA schema_name;

Create new schema

SET search_path TO schema_name;

Change default schema

Tables

CREATE TABLE t (id SERIAL PRIMARY KEY, name VARCHAR(50));

Create table with auto-incrementing ID

DROP TABLE t CASCADE;

Drop table and dependent objects (e.g. views)

TRUNCATE TABLE t RESTART IDENTITY;

Empty table and reset sequences

ALTER TABLE t ADD COLUMN c TEXT;

Add column

ALTER TABLE t DROP COLUMN c;

Remove column

ALTER TABLE t RENAME COLUMN a TO b;

Rename column

ALTER TABLE t ALTER COLUMN c TYPE INT USING c::INT;

Change column data type

CRUD

SELECT * FROM t;

Retrieve all rows and columns

INSERT INTO t (c) VALUES ('v') RETURNING id;

Insert and return generated ID

UPDATE t SET c='v' WHERE id=1;

Update row

DELETE FROM t WHERE id=1;

Delete row

INSERT INTO t (id, c) VALUES (1, 'v') ON CONFLICT (id) DO UPDATE SET c = EXCLUDED.c;

Upsert (Insert or Update on conflict)

Queries

SELECT DISTINCT c FROM t;

Select unique values

SELECT * FROM t WHERE c ILIKE '%pattern%';

Case-insensitive pattern matching

SELECT * FROM t WHERE c IS NULL;

Find records with NULL values

SELECT COALESCE(num_col, 0) FROM t;

Return first non-null value (use 0 if null)

SELECT * FROM t LIMIT 10 OFFSET 20;

Paginate (limit num rows, offset start)

Joins

SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;

Inner join

SELECT * FROM t1 LEFT JOIN t2 USING (common_id);

Left join with USING syntax (when column names match)

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id;

Full outer join (records from both tables)

JSON & Arrays

SELECT json_col->'key' FROM t;

Extract JSON object field as JSON

SELECT json_col->>'key' FROM t;

Extract JSON object field as text

SELECT * FROM t WHERE json_col @> '{"k":"v"}';

Find document containing key/value

SELECT arr_col[1] FROM t;

Access first element of array (1-indexed)

SELECT * FROM t WHERE 'val' = ANY(arr_col);

Find row where array contains value

Advanced Queries

SELECT c, COUNT(*) FROM t GROUP BY c;

Group and count

SELECT string_agg(name, ', ') FROM t;

Concatenate values into a single string

SELECT id, ROW_NUMBER() OVER(PARTITION BY cat ORDER BY date) FROM t;

Window function: rank items within groups

Roles & Privileges

CREATE ROLE name WITH LOGIN PASSWORD 'pwd';

Create a user role

GRANT ALL PRIVILEGES ON DATABASE db TO name;

Grant database privileges

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA pub TO name;

Grant table privileges

Performance

CREATE INDEX idx_name ON t (col);

Create a B-tree index

CREATE INDEX idx_name ON t USING GIN (json_col);

Create GIN index (for JSONB/Arrays)

CREATE INDEX idx_name ON t (col) WHERE active = true;

Create partial index

EXPLAIN ANALYZE SELECT * FROM t;

Execute query and show actual run times

VACUUM ANALYZE t;

Reclaim storage and update statistics

SELECT pid, query FROM pg_stat_activity WHERE state = 'active';

Show active connections and queries

Advanced

Advanced Command Snippet

Explore advanced configurations for Postgres

System Optimization

Performance tuning best practices for Postgres

Security Audit

Run security checks and validation for Postgres

320 x 50 Mobile Ad

🔥 Similar Tools

Explore Library
Linux Commands
Python Cheat Sheet
PHP Cheat Sheet
JavaScript Cheat Sheet

Frequently Asked Questions

Got questions? We've got answers.

The Postgres Cheatsheet is a quick-reference guide that provides developers with the most essential and frequently used Postgres commands, syntax, and snippets in one centralized place.
This reference is built for both beginners who are just learning Postgres and need a quick syntax lookup, as well as seasoned professionals who need to jog their memory on complex commands.
You can use the real-time search bar at the top of the cheatsheet. Simply type a keyword (like 'delete' or 'file') and the list will instantly filter to show only matching Postgres commands.
Yes! Every command block features a one-click copy button. Just hover over the command and click the copy icon to instantly send the snippet to your clipboard.
The cheatsheet is divided into logical categories such as Basics, Network, Operations, and specific Postgres features. You can click on the category filters at the top to isolate specific groups of commands.
Absolutely. This Postgres reference guide is 100% free, requiring no sign-ups or subscriptions, and is always available when you need it.
While these are standard Postgres operations, you should always understand what a command does before running it, especially if it involves system operations or destructive actions.
Yes, we have recently expanded this cheatsheet to include advanced snippets, best practices, and edge-case syntax that go beyond basic introductory commands.
ADVERTISEMENT
Boost Your Business Online