PostgreSQL SQL Cheat Sheet
Quick reference guide for PostgreSQL: JSONB, window functions, indexing, CTEs, and psql CLI.
Databases
postgresql
postgres
sql
PostgreSQL is a powerful, open-source object-relational database system known for reliability, feature robustness, and performance with complex queries.
Essential `psql` CLI Commands
Table
| Command | Action / Purpose |
|---|---|
psql -U username -d dbname | Connect to PostgreSQL database |
\l | List all databases on server |
\c dbname | Connect to a different database |
\dt | List all tables in current database schema |
\d tablename | Describe table columns, data types, and indexes |
\ef function_name | Edit PostgreSQL function definition |
\q | Quit psql interactive terminal |
JSONB Operations & Query Operators
PostgreSQL provides native JSONB indexing and query operators for semi-structured data.
Table
| Operator | Meaning / Action | SQL Example |
|---|---|---|
-> | Extract JSON element by key (returns JSON) | data->'user' |
->> | Extract JSON element as text | data->>'email' |
#> | Extract nested JSON by path | data#>'{user, address}' |
@> | Check if JSONB contains target | data @> '{"role": "admin"}' |
? | Check if string exists as top-level key | data ? 'active' |
sql
-- Querying JSONB columns
SELECT id, data->>'name' AS user_name
FROM users
WHERE data @> '{"active": true}';
Common Table Expressions (CTEs) & Window Functions
sql
-- CTE (WITH clause) example
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 10000;
-- Window function (ROW_NUMBER & OVER)
SELECT id, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
Indexing Strategies
Table
| Index Type | Best Use Case | SQL Example |
|---|---|---|
| B-Tree | Equality and range queries (=, <, >) | CREATE INDEX idx_user_email ON users(email); |
| GIN | JSONB, arrays, and full-text search | CREATE INDEX idx_user_data_gin ON users USING gin(data); |
| Partial Index | Indexing a subset of rows | CREATE INDEX idx_active_users ON users(email) WHERE active IS true; |
Common Pitfalls & Tips
[!WARNING] Using
->returns JSON objects or quoted strings; use->>when comparing JSON values against text literals in SQLWHEREclauses.
[!TIP] Always run
EXPLAIN ANALYZE SELECT ...to inspect the query execution plan and verify that indexes are being utilized properly.