PostgreSQL Cheat Sheet

psql -d appdb

psql postgresql://appuser:secret@localhost:5432/appdb

\l

\du

\dn

\dt *.*

\d+ public.users

\x auto

\timing on

psql -d appdb -f schema.sql

CREATE DATABASE appdb;

CREATE DATABASE appdb OWNER appuser TEMPLATE template0 ENCODING 'UTF8';

ALTER DATABASE appdb RENAME TO appdb_prod;

DROP DATABASE appdb;

CREATE ROLE appuser WITH LOGIN PASSWORD 'secret';

CREATE ROLE readonly WITH LOGIN PASSWORD 'secret' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT;

ALTER ROLE appuser WITH PASSWORD 'new-secret';

GRANT readonly TO analyst_user;

REVOKE readonly FROM analyst_user;

DROP ROLE readonly;

CREATE SCHEMA accounting AUTHORIZATION appuser;

SET search_path TO accounting, public;

CREATE TABLE public.users (
  user_id bigserial PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

ALTER TABLE public.users ADD COLUMN last_login_at timestamptz;

ALTER TABLE public.users ALTER COLUMN created_at SET DEFAULT now();

ALTER TABLE public.users ALTER COLUMN email SET NOT NULL;

ALTER TABLE orders ADD CONSTRAINT orders_total_nonnegative CHECK (total_amount >= 0);

ALTER TABLE public.users RENAME TO app_users;

DROP TABLE IF EXISTS public.temp_import;

INSERT INTO public.users (email) VALUES ('a@example.com');

INSERT INTO public.users (email)
VALUES ('a@example.com'), ('b@example.com'), ('c@example.com');

INSERT INTO public.users (email)
VALUES ('d@example.com')
RETURNING user_id, email, created_at;

INSERT INTO public.users (email)
VALUES ('a@example.com')
ON CONFLICT (email) DO UPDATE
SET created_at = now();

UPDATE public.users SET last_login_at = now() WHERE user_id = 42;

UPDATE orders o
SET customer_tier = c.tier
FROM customers c
WHERE o.customer_id = c.customer_id;

DELETE FROM sessions WHERE expires_at < now();

DELETE FROM jobs WHERE status = 'done' RETURNING job_id;

MERGE INTO inventory AS i
USING inventory_stage AS s
ON i.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

TRUNCATE TABLE audit_log RESTART IDENTITY;

SELECT user_id, email FROM public.users ORDER BY user_id DESC LIMIT 20;

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

SELECT customer_id, count(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING count(*) >= 5;

SELECT *, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC)
FROM orders;

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at >= now() - interval '7 days'
)
SELECT customer_id, count(*)
FROM recent_orders
GROUP BY customer_id;

WITH RECURSIVE nums(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

SELECT user_id, unnest(tags) AS tag FROM user_profiles;

SELECT payload->>'event_type' AS event_type FROM events;

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

CREATE UNIQUE INDEX idx_users_email_unique ON public.users (email);

CREATE INDEX idx_orders_open_only ON orders (created_at) WHERE status = 'open';

CREATE INDEX idx_users_lower_email ON public.users ((lower(email)));

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);

ALTER TABLE invoices ADD CONSTRAINT invoices_pkey PRIMARY KEY (invoice_id);

ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE RESTRICT;

ALTER TABLE public.users ADD CONSTRAINT users_email_key UNIQUE (email);

DROP INDEX IF EXISTS idx_orders_open_only;

CREATE VIEW active_users AS
SELECT user_id, email
FROM public.users
WHERE deleted_at IS NULL;

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, sum(total_amount) AS revenue
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

CREATE FUNCTION public.user_email_domain(email text)
RETURNS text
LANGUAGE sql
AS $$
  SELECT split_part(email, '@', 2);
$$;

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON public.users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

CREATE EXTENSION IF NOT EXISTS pgcrypto;

\dx

COMMENT ON TABLE public.users IS 'Application users';

DROP FUNCTION IF EXISTS public.user_email_domain(text);

SELECT jsonb_build_object('user_id', user_id, 'email', email) FROM public.users LIMIT 5;

UPDATE events
SET payload = jsonb_set(payload, '{status}', '"processed"', true)
WHERE event_id = 1;

SELECT * FROM events WHERE payload @> '{"event_type":"signup"}'::jsonb;

UPDATE user_profiles SET tags = array_append(tags, 'vip') WHERE user_id = 42;

SELECT * FROM user_profiles WHERE 'vip' = ANY(tags);

SELECT date_trunc('hour', created_at) AS hour, count(*)
FROM events
GROUP BY 1
ORDER BY 1;

SELECT now() - interval '30 days';

SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles'
FROM events
LIMIT 10;

SELECT generate_series(date '2026-01-01', date '2026-01-07', interval '1 day');

SELECT user_id, COALESCE(display_name, email) AS name FROM public.users;