PostgreSQL Cheat Sheet

New

PostgreSQL commands, data types, queries, indexes, constraints, and administration

psql Commands

Connection & Navigation

Essential psql meta-commands for navigating databases

\l          -- list all databases
\c dbname   -- connect to database
\dt         -- list tables in current DB
\d tablename -- describe table structure
\di         -- list indexes
\du         -- list roles/users
\q          -- quit psql

Useful psql Settings

Display and output settings

\x          -- toggle expanded output
\timing     -- toggle query timing
\e          -- open query in editor
\i file.sql -- execute SQL file
\o file.txt -- send output to file

Data Types

Numeric & Text

Common data types for columns

integer / int4     -- 32-bit integer
bigint / int8      -- 64-bit integer
serial             -- auto-increment integer
bigserial          -- auto-increment bigint
numeric(p,s)       -- exact decimal
real / float4      -- 32-bit float
varchar(n)         -- variable-length string (max n)
text               -- unlimited string
char(n)            -- fixed-length string

Date, Boolean & Special

Date/time, boolean, JSON, and UUID types

boolean            -- true/false
date               -- YYYY-MM-DD
timestamp          -- date and time
timestamptz        -- with time zone (preferred)
interval           -- time duration
jsonb              -- binary JSON (indexed)
json               -- text JSON
uuid               -- universally unique ID
array              -- e.g. integer[]

DDL (Schema)

CREATE TABLE

Create a new table with columns and constraints

CREATE TABLE users (
  id      BIGSERIAL PRIMARY KEY,
  email   VARCHAR(255) NOT NULL UNIQUE,
  name    TEXT NOT NULL,
  active  BOOLEAN DEFAULT true,
  created TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE

Modify an existing table structure

ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN email TYPE TEXT;

CREATE INDEX

Create indexes for faster queries

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE UNIQUE INDEX idx_unique_slug ON posts(slug);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

DML (Queries)

SELECT

Query data with filtering, sorting, and pagination

SELECT * FROM users WHERE active = true
ORDER BY created DESC
LIMIT 20 OFFSET 40;

SELECT id, name, email FROM users
WHERE name ILIKE '%alice%'
  AND created > NOW() - INTERVAL '7 days';

INSERT

Insert one or many rows

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

INSERT INTO users (name, email)
SELECT name, email FROM old_users
WHERE active = true
RETURNING id, email;

UPDATE & DELETE

Modify or remove existing rows

UPDATE users SET active = false
WHERE last_login < NOW() - INTERVAL '90 days'
RETURNING id;

DELETE FROM users WHERE id = 123
RETURNING *;

JOINs

JOIN Types

Different join types and when to use each

-- INNER JOIN (only matching rows)
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON p.user_id = u.id;

-- LEFT JOIN (all users, even without 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.id;

Aggregates & Window

Aggregate Functions

Group and summarize data

SELECT user_id, COUNT(*) AS total_posts
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_posts DESC;

-- Common aggregates
COUNT(*), COUNT(DISTINCT col)
SUM(col), AVG(col)
MIN(col), MAX(col)
ARRAY_AGG(col), STRING_AGG(col, ',')

Window Functions

Compute values across related rows without collapsing groups

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

Constraints

Common Constraints

Enforce data integrity rules

PRIMARY KEY           -- unique, not null identifier
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
UNIQUE (email)
NOT NULL
CHECK (age >= 0 AND age < 150)
DEFAULT NOW()

Common Patterns

Create Table with Constraints

Well-structured table with foreign keys, indexes, and defaults

CREATE TABLE posts (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  slug        VARCHAR(255) NOT NULL UNIQUE,
  title       TEXT NOT NULL,
  content     TEXT,
  status      VARCHAR(20) NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft', 'published', 'archived')),
  published_at TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status) WHERE status = 'published';
CREATE INDEX idx_posts_created ON posts(created_at DESC);

Query with JOIN

Retrieve related data with aggregates

-- Get users with their post count and latest post date
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(p.id) AS post_count,
  MAX(p.created_at) AS last_post_at
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'published'
WHERE u.active = true
GROUP BY u.id, u.name, u.email
HAVING COUNT(p.id) > 0
ORDER BY last_post_at DESC NULLS LAST
LIMIT 10;

Upsert with ON CONFLICT

Insert or update atomically using ON CONFLICT

-- Upsert: insert or update if conflict
INSERT INTO user_settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET
  value = EXCLUDED.value,
  updated_at = NOW();

-- Ignore conflict (do nothing)
INSERT INTO user_follows (follower_id, followee_id)
VALUES (1, 2)
ON CONFLICT (follower_id, followee_id)
DO NOTHING;