# Connect
psql -U username -d database_name
psql -h localhost -p 5432 -U username -d mydb
Connection string
psql “postgresql://user:pass@localhost:5432/mydb”
Connect to default database
psql -U postgres

\l          -- List all databases
\c mydb     -- Connect to database
\dt         -- List tables in current schema
\dt+        -- List tables with sizes
\d users    -- Describe table (columns, types, indexes)
\di         -- List indexes
\dv         -- List views
\df         -- List functions
\dn         -- List schemas
\du         -- List roles/users
\dx         -- List extensions

\q          -- Quit
\?          -- Help for psql commands
\h SELECT   -- Help for SQL commands
\timing     -- Toggle query timing
\x          -- Toggle expanded display (vertical output)
\e          -- Open query in editor
\i file.sql -- Execute SQL file
\o out.txt  -- Send output to file
\! ls       -- Run shell command

-- Numbers
INTEGER (INT)          -- -2B to 2B
BIGINT                 -- -9Q to 9Q
SERIAL                 -- Auto-increment integer
BIGSERIAL              -- Auto-increment bigint
NUMERIC(10,2)          -- Exact decimal (money, etc.)
REAL                   -- 6 decimal digits
DOUBLE PRECISION       -- 15 decimal digits
— Text
VARCHAR(255)           — Variable length, max 255
TEXT                   — Unlimited length
CHAR(10)               — Fixed length, padded
— Date/Time
DATE                   — 2026-03-14
TIME                   — 14:30:00
TIMESTAMP              — 2026-03-14 14:30:00
TIMESTAMPTZ            — With timezone (preferred!)
INTERVAL               — Time span
— Other
BOOLEAN                — true/false
UUID                   — gen_random_uuid()
JSONB                  — Binary JSON (preferred over JSON)
ARRAY                  — INTEGER[], TEXT[]
BYTEA                  — Binary data
INET                   — IP address

Use TIMESTAMPTZ over TIMESTAMP and JSONB over JSON — almost always.

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name TEXT NOT NULL,
  role VARCHAR(50) DEFAULT 'user',
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
— With foreign key
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
body TEXT,
published BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Single row
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice');
— Multiple rows
INSERT INTO users (email, name) VALUES
(‘bob@example.com’, ‘Bob’),
(‘carol@example.com’, ‘Carol’);
— Insert and return the new row
INSERT INTO users (email, name)
VALUES (‘dave@example.com’, ‘Dave’)
RETURNING id, email;
— Insert or update (upsert)
INSERT INTO users (email, name)
VALUES (‘alice@example.com’, ‘Alice Updated’)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- Basics
SELECT * FROM users WHERE role = 'admin';
SELECT name, email FROM users ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10 OFFSET 20;
— Aggregates
SELECT role, COUNT() FROM users GROUP BY role;
SELECT role, COUNT() FROM users GROUP BY role HAVING COUNT(*) > 5;
— Joins
SELECT u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE p.published = true;
— Left join (include users with no posts)
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.name;
— Subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts WHERE published = true);
— CTE (Common Table Expression)
WITH active_users AS (
SELECT * FROM users WHERE last_login > NOW() - INTERVAL ‘30 days’
)
SELECT * FROM active_users WHERE role = ‘admin’;

-- Update
UPDATE users SET name = 'Alice B.' WHERE email = 'alice@example.com';
UPDATE users SET role = 'admin' WHERE id IN (1, 2, 3);
— Update with RETURNING
UPDATE users SET role = ‘admin’ WHERE id = 1 RETURNING *;
— Delete
DELETE FROM users WHERE id = 5;
DELETE FROM users WHERE last_login < NOW() - INTERVAL ‘1 year’;
— Truncate (delete all rows, fast)
TRUNCATE TABLE logs;

-- B-tree (default, good for =, <, >, BETWEEN)
CREATE INDEX idx_users_email ON users(email);
— Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);
— Composite index
CREATE INDEX idx_posts_user_published ON posts(user_id, published);
— Partial index (only index some rows)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
— GIN index (for JSONB, arrays, full-text)
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
— Drop index
DROP INDEX idx_users_email;

-- See query plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
— See actual execution time
EXPLAIN ANALYZE SELECT * FROM users WHERE email = ‘alice@example.com’;
— Look for:
— Seq Scan = full table scan (slow on big tables)
— Index Scan = using an index (good)
— Nested Loop = might be slow with many rows

-- Access a key
SELECT metadata->>'theme' FROM users;       -- as text
SELECT metadata->'settings' FROM users;      -- as JSON
— Nested access
SELECT metadata->‘settings’->>‘language’ FROM users;
— Filter by JSON value
SELECT * FROM users WHERE metadata->>‘role’ = ‘admin’;
— Check if key exists
SELECT * FROM users WHERE metadata ? ‘theme’;
— Contains
SELECT * FROM users WHERE metadata @> ’{“role”: “admin”}’;
— Update a key
UPDATE users SET metadata = metadata || ’{“theme”: “dark”}’ WHERE id = 1;
— Remove a key
UPDATE users SET metadata = metadata - ‘theme’ WHERE id = 1;

# Backup
pg_dump -U postgres mydb > backup.sql
pg_dump -U postgres -Fc mydb > backup.dump   # Custom format (compressed)
Restore
psql -U postgres mydb < backup.sql
pg_restore -U postgres -d mydb backup.dump
Backup specific table
pg_dump -U postgres -t users mydb > users.sql

-- Add column
ALTER TABLE users ADD COLUMN bio TEXT;
— Drop column
ALTER TABLE users DROP COLUMN bio;
— Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;
— Change type
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(500);
— Add constraint
ALTER TABLE users ADD CONSTRAINT email_check CHECK (email LIKE ’%@%’);
— Rename table
ALTER TABLE users RENAME TO accounts;