Data Types
- INTEGER/BIGINTWhole numbers
- NUMERIC(p,s)Exact decimal
- VARCHAR(n)Variable string
- TEXTUnlimited text
- BOOLEANtrue/false
- TIMESTAMPDate and time
- UUIDUnique ID
- JSONBBinary JSON
psql Commands
- \lList databases
- \c dbnameConnect to DB
- \dtList tables
- \d tablenameDescribe table
- \diList indexes
- \duList users
- \qQuit psql
Common Functions
- COUNT(*)Count rows
- SUM(col)Sum values
- AVG(col)Average
- COALESCE()First non-null
- NOW()Current time
- EXTRACT()Date part
User Management
-- Create user
CREATE USER myuser
WITH PASSWORD 'secret';
-- Grant privileges
GRANT ALL PRIVILEGES ON
DATABASE mydb TO myuser;
-- Grant table access
GRANT SELECT, INSERT ON
users TO myuser;
-- Revoke access
REVOKE ALL ON users
FROM myuser;
Table Creation
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
metadata JSONB,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
total NUMERIC(10,2) NOT NULL,
CHECK (total > 0)
);
Query Examples
-- Basic select with join
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;
-- Subquery
SELECT * FROM users
WHERE id IN (
SELECT user_id FROM orders
WHERE total > 100
);
Indexes
-- B-tree index (default)
CREATE INDEX idx_email
ON users(email);
-- Unique index
CREATE UNIQUE INDEX idx_uniq
ON users(email);
-- Partial index
CREATE INDEX idx_active
ON users(status)
WHERE status = 'active';
-- GIN index for JSONB
CREATE INDEX idx_meta
ON users USING GIN(metadata);
Window Functions
SELECT
name,
amount,
ROW_NUMBER() OVER(
ORDER BY amount DESC
) AS rank,
SUM(amount) OVER(
PARTITION BY category
) AS category_total,
LAG(amount) OVER(
ORDER BY date
) AS prev_amount
FROM sales;
Common Table Expressions (CTE)
-- Basic CTE
WITH active_users AS (
SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';
-- Recursive CTE (hierarchical data)
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;
JSONB Operations
- ->Get JSON element
- >>Get as text
- @>Contains
- ||Concatenate
- jsonb_set()Update value
Maintenance
- VACUUMReclaim space
- ANALYZEUpdate stats
- REINDEXRebuild indexes
- pg_dumpBackup DB
- pg_restoreRestore backup