psql Commands
| Command | Description |
|---|
\l | List databases |
\c dbname | Connect to database |
\dt | List tables |
\d tablename | Describe table |
\di | List indexes |
\q | Quit |
JSON Operations
-- Access JSON field
SELECT data->>'name' FROM users;
-- Nested access
SELECT data->'address'->>'city' FROM users;
-- Filter by JSON value
SELECT * FROM users WHERE data->>'role' = 'admin';
-- JSONB containment
SELECT * FROM users WHERE data @> '{"active": true}';
CTEs (Common Table Expressions)
WITH active_users AS (
SELECT * FROM users WHERE active = true
),
user_orders AS (
SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id
)
SELECT u.name, COALESCE(o.cnt, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;
Window Functions
-- Row number
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- Running total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;
-- Partition
SELECT department, name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;
Create Table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
data JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_data ON users USING GIN (data);
