Database Cheat Sheet

Common commands, data types, functions, and gotchas for PostgreSQL, MySQL, SQLite, and SQL Server.

Database & Tables

List databases▼

\l
SELECT datname FROM pg_database;

List tables▼

\dt
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

Describe table▼

\d table_name
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'my_table';

Create table▼

CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

Drop table▼

DROP TABLE IF EXISTS users CASCADE;

Add column▼

ALTER TABLE users ADD COLUMN age INTEGER;

Rename column▼

ALTER TABLE users RENAME COLUMN name TO full_name;

Drop column▼

ALTER TABLE users DROP COLUMN age;

Queries

Select with join▼

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.total > 100
ORDER BY o.total DESC
LIMIT 10;

Upsert▼

INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

CTE▼

WITH active_users AS (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users WHERE created_at > now() - interval '30 days';

Window function▼

SELECT name, total,
  ROW_NUMBER() OVER (ORDER BY total DESC) as rank,
  SUM(total) OVER () as grand_total
FROM orders;

JSON query▼

SELECT data->>'name' as name,
  data->'address'->>'city' as city
FROM users
WHERE data @> '{"active": true}';

Array operations▼

SELECT * FROM users WHERE 'admin' = ANY(roles);
SELECT ARRAY_AGG(DISTINCT tag) FROM posts;

Indexes & Performance

Create index▼

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX CONCURRENTLY idx_orders_date ON orders (created_at);

Unique index▼

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

Partial index▼

CREATE INDEX idx_active_users ON users (email) WHERE active = true;

GIN index (JSON)▼

CREATE INDEX idx_users_data ON users USING GIN (data);

Explain query▼

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = '[email protected]';

Table stats▼

SELECT relname, n_live_tup, n_dead_tup,
  pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables;

Common Gotchas

NULL comparison▼

-- WRONG: WHERE col = NULL
-- RIGHT: WHERE col IS NULL
-- RIGHT: WHERE col IS NOT NULL

Case sensitivity▼

-- PostgreSQL identifiers are lowercased unless quoted
-- "MyTable" preserves case, mytable doesn't
SELECT * FROM "MyTable"; -- quoted
SELECT * FROM mytable;   -- unquoted (lowercased)

String concat▼

-- Use || for concatenation
SELECT first_name || ' ' || last_name AS full_name;

LIMIT vs FETCH▼

-- PostgreSQL supports both
SELECT * FROM users LIMIT 10;
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;

Database Access

Give Your AI Agents
Database Access. Securely.

Connect any database. Control permissions. Audit every query. All running locally on your machine.

Download for macOS