PostgreSQL¶

# Connect to database
psql -h localhost -U myuser -d mydb

# Common psql commands
\l                    # List databases
\dt                   # List tables
\d tablename          # Describe table (columns, indexes)
\du                   # List users/roles
\x                    # Toggle expanded display
\q                    # Quit

# Create database and user
CREATE DATABASE mydb;
CREATE USER myuser WITH PASSWORD 'secure_pass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

# Backup and restore
pg_dump mydb > backup.sql
pg_dump -h remote_host mydb > remote_backup.sql
psql mydb < backup.sql

# Performance
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
SELECT pg_size_pretty(pg_database_size('mydb'));

-- Indexing strategies
CREATE INDEX idx_users_email ON users (email);                    -- Single column
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);  -- Composite
CREATE UNIQUE INDEX idx_unique_username ON users (username);        -- Unique constraint

-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'postgresql & performance');

-- JSON operations
SELECT data->>'name' AS name, data->>'email' AS email
FROM users WHERE data @> '{"active": true}';

-- Window functions
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- Common Table Expressions (CTEs)
WITH popular_products AS (
    SELECT product_id, COUNT(*) as sales
    FROM orders GROUP BY product_id HAVING COUNT(*) > 100
)
SELECT p.name, pp.sales
FROM products p JOIN popular_products pp ON p.id = pp.product_id;