PostgreSQL Cheatsheet

PostgreSQL Cheatsheet

Installation & Setup

# Install PostgreSQL (Debian/Ubuntu)
sudo apt update
sudo apt install postgresql postgresql-contrib

# Switch to postgres user
sudo -i -u postgres
psql

# Access a specific database with a user
psql -d my_database -U my_user -h localhost

# List all databases
\l

# List all tables in current database
\dt

# List all users/roles
\du

# Show table structure (describe)
\d table_name

# Show table of columns
\d+ table_name

Basic SQL Operations

CRUD Operations

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT_NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) 
VALUES ('johndoe', 'john@example.com');

-- Select data
SELECT * FROM users WHERE username = 'johndoe';
SELECT id, username FROM users LIMIT 10;

-- Update data
UPDATE users SET email = 'john_new@example.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

Joins & Relationships

-- Inner Join
SELECT orders.id, users.username 
FROM orders 
JOIN users ON orders.user_id = users.id;

-- Left Join (all users, even without orders)
SELECT users.username, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

-- Aggregation (Count/Sum)
SELECT count(*) FROM users;
SELECT sum(amount), category FROM sales GROUP BY category;

Administration & Maintenance

Backup and Restore

# Dump a single database to a file
pg_dump -U username -d db_name > db_backup.sql

# Restore from a SQL file
psql -U username -d db_name < db_backup.sql

# Full PostgreSQL cluster backup (all DBs)
pg_dumpall -U username > full_cluster_backup.sql

# Restore full cluster
psql -U username -f full_cluster_backup.sql

User & Permission Management

-- Create a new user
CREATE USER web_user WITH PASSWORD 'strong_password';

-- Grant privileges on a table
GRANT SELECT, INSERT, UPDATE ON users TO web_user;

-- Grant all privileges on a database
GRANT ALL PRIVILEGES ON DATABASE my_db TO web_user;

-- Revoke privileges
REVOKE UPDATE ON users FROM web_user;

-- Create a role/group
CREATE ROLE readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Indexing & Performance

-- Create B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- Create GIN index (for JSONB)
CREATE INDEX idx_meta_json ON metadata USING GIN (data);

-- Analyze table (Update statistics)
ANALYZE users;

-- Explain Query Plan (Optimization)
EXPLAIN ANALYYZE 
SELECT * FROM users WHERE email = 'john@example.com';

Common Functions & Types

-- Current timestamp
SELECT now();

-- Concatenation
SELECT first_name || ' ' || last_name FROM employees;

-- String Manipulation
SELECT UPPER(username) FROM users;
SELECT substring(email from '@(.*)') FROM users;

-- JSONB Operations
SELECT data->>'name' FROM product_metadata;
SELECT data @> '{"category": "electronics"}' FROM products;

Maintenance Commands

-- Reindex a table
REINDEX TABLE users;

-- Vacuum (Clean up dead tuples/optimize)
VACUUM users;
VACUUM FULL users;  -- Reclaims space but locks the table

-- Truncate a table (Fast delete)
TRUNCATE TABLE logs;

Troubleshooting

-- List active connections
SELECT pid, usename, client_addr, query 
FROM pg_stat_activity;

-- Kill a specific backend process
SELECT pg-terminate-backend(PID);

-- Check table size
SELECT pg_size_pretty(pg_relation_size('users'));

-- Check for long-running transactions
SELECT pid, now() - xact_start AS duration, query 
FROM pg_stat_activity 
WHERE state != 'idle' ORDER BY duration DESC;

Best Practices

1. Always use transactions for multi-step operations to ensure data integrity (BEGIN; ... COMMIT;).
2. Prefer UUID or BIGSERIAL for primary keys in high-growth tables.
3. Use EXPLAIN ANALYZE to understand why a query is slow.
4. Always index columns used in WHERE clauses and JOIN conditions.
5. Automate VACUUM; ensure autovacuum is running in production.
6. Use JSONB instead of JSON for better performance and indexing capabilities.
7. Avoid SELECT * in production code; explicitly name the columns you need.
8. Keep credentials safe: Use .pgpass or environment variables instead of hardcoded passwords.
9. Handle locks carefully: Be aware of table locks during ALTER TABLE or VACUUM FULL.
10. Regularly monitor connection counts and transaction rates.

---

Relational Database Management System (RDBMS) Reference