PostgreSQL Reference

# Connect to 'mydb' as 'user' on 'localhost'
psql -h localhost -U user -d mydb

# Execute a single command and exit
psql -c "SELECT version();"

\l          -- List all databases
\c mydb     -- Connect to 'mydb'
\dt         -- List all tables in current schema
\d+ users   -- Describe table 'users' in detail
\i file.sql -- Execute commands from file.sql
\q          -- Quit psql

-- Single-line comment

/* Multi-line
   comment block */
SELECT 1; -- Trailing comment

SELECT 
    42::SMALLINT,        -- 2-byte int
    100000::INTEGER,     -- 4-byte int
    10000000000::BIGINT, -- 8-byte int
    3.1415::NUMERIC(6,4),-- Exact decimal
    'NaN'::REAL;         -- 4-byte float

SELECT 
    'fixed'::CHAR(5),       -- Padded string
    'var'::VARCHAR(10),     -- Limited string
    'unlimited'::TEXT,      -- Preferred for most text
    '\xDEADBEEF'::BYTEA;    -- Variable-length binary

SELECT 
    CURRENT_DATE,                             -- Date only
    CURRENT_TIME,                             -- Time with TZ
    NOW() AT TIME ZONE 'UTC',                 -- Timestamp without TZ
    INTERVAL '1 day 2 hours' AS duration;     -- Time span

CREATE TYPE user_status AS ENUM ('active', 'banned');
SELECT 
    true::BOOLEAN AS is_ok,
    'active'::user_status;

SELECT 
    first_name || ' ' || last_name AS full_name,
    UPPER(email) AS contact_email
FROM users;

SELECT * FROM products
WHERE price > 100 
  AND category IN ('electronics', 'tools')
  AND name ~* '^pro.*'; -- Case-insensitive POSIX regex

SELECT name, price FROM products
ORDER BY category ASC, price DESC NULLS LAST;

SELECT id FROM events
ORDER BY created_at DESC
LIMIT 10 OFFSET 20; -- Skip first 20, take next 10

SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- Users with no orders

-- Self join for hierarchical data
SELECT e.name AS emp, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

SELECT email FROM leads
UNION -- Removes duplicates (UNION ALL keeps them)
SELECT email FROM customers
EXCEPT
SELECT email FROM unsubscribed;

SELECT u.name, recent.max_date
FROM users u
JOIN (
    SELECT user_id, MAX(created_at) AS max_date 
    FROM logins GROUP BY user_id
) AS recent ON u.id = recent.user_id;

SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category;

SELECT user_id, SUM(total)
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

SELECT 
    COUNT(DISTINCT category) AS cats,
    STRING_AGG(name, ', ' ORDER BY name) AS all_names,
    ARRAY_AGG(id) AS id_list
FROM products;

SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM salaries s 
    WHERE s.emp_id = e.id AND s.amount > 100000
);

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total FROM sales GROUP BY region
)
SELECT region FROM regional_sales 
WHERE total > (SELECT AVG(total) FROM regional_sales);

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id 
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

SELECT 
    name, 
    price,
    AVG(price) OVER(PARTITION BY category) AS cat_avg
FROM products;

SELECT 
    name, 
    ROW_NUMBER() OVER(ORDER BY score DESC) AS pos,
    RANK() OVER(ORDER BY score DESC) AS rnk
FROM players;

SELECT 
    day, 
    value,
    LAG(value) OVER(ORDER BY day) AS prev_val,
    value - LAG(value) OVER(ORDER BY day) AS delta
FROM daily_stats;

INSERT INTO users (name, email)
VALUES ('Bob', 'bob@example.com')
RETURNING id, created_at; -- Get generated values back immediately

UPDATE products SET price = price * 1.1 
WHERE category = 'luxury'
RETURNING name, price AS new_price;

DELETE FROM sessions WHERE last_seen < NOW() - INTERVAL '1 hour';

INSERT INTO user_prefs (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id) 
DO UPDATE SET theme = EXCLUDED.theme, updated_at = NOW();

CREATE TABLE items (
    id SERIAL PRIMARY KEY, -- Auto-incrementing 4-byte int
    uuid UUID DEFAULT gen_random_uuid(),
    metadata JSONB NOT NULL DEFAULT '{}'
);

ALTER TABLE items ADD COLUMN tags TEXT[] DEFAULT '{}';

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL CHECK (email ~* '@'),
    balance NUMERIC DEFAULT 0 CHECK (balance >= 0),
    parent_id INT REFERENCES accounts(id) ON DELETE CASCADE
);

CREATE INDEX idx_user_email ON users (email);
CREATE INDEX idx_item_meta ON items USING GIN (metadata); -- GIN for JSONB

CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

-- Materialized view requires manual refresh
CREATE MATERIALIZED VIEW sales_summary AS 
SELECT region, SUM(amount) FROM sales GROUP BY region;

REFRESH MATERIALIZED VIEW sales_summary;

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Or ROLLBACK on error

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_items 
BEFORE UPDATE ON items 
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 500;

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

CREATE SCHEMA archive;
ALTER TABLE old_logs SET SCHEMA archive;