133 commands · 9 cheat sheets · SQL
Modeling and Data Types master quick reference
Browse 133 commands from 9 focused cheat sheets on page 1 of 1. Each example opens its matching detailed section.
Modeling and Data Types · 14 commands
PostgreSQL Arrays, Ranges, and Multiranges Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Construct a typed array | ARRAY['read', 'write']::text[] | View examples |
| Read the first element | permissions[1] | View examples |
| Count all elements | cardinality(permissions) | View examples |
| Match any element | 'admin' = ANY (permissions) | View examples |
| Test array containment | permissions @> ARRAY['read', 'write']::text[] | View examples |
| Test array overlap | permissions && ARRAY['admin', 'owner']::text[] | View examples |
| Expand with positions | CROSS JOIN LATERAL unnest(t.tags)
WITH ORDINALITY AS tag(value, position) | View examples |
| Create a half-open range | tstzrange(starts_at, ends_at, '[)') | View examples |
| Test range containment | valid_during @> now() | View examples |
| Test range overlap | reserved_during && tstzrange($1, $2, '[)') | View examples |
| Prevent overlapping bookings | EXCLUDE
USING gist (room_id
WITH =, reserved_during
WITH &&
) | View examples |
| Construct a date multirange | datemultirange(daterange('2026-08-01', '2026-08-08', '[)'), daterange('2026-08-15', '2026-08-22', '[)')) | View examples |
| Index array operators | CREATE INDEX documents_tags_gin_idx
ON app.documents
USING gin (tags); | View examples |
| Index range operators | CREATE INDEX bookings_during_gist_idx
ON app.bookings
USING gist (reserved_during); | View examples |
Modeling and Data Types · 14 commands
PostgreSQL Binary Data: bytea and Large Objects Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a bytea column | CREATE TABLE app.assets (asset_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body bytea NOT NULL); | View examples |
| Write a hex literal | INSERT INTO app.assets (body)
VALUES ('\x89504e47'::bytea); | View examples |
| Decode text into bytes | SELECT decode('89504e47', 'hex'); | View examples |
| Encode bytes as base64 | SELECT encode(body, 'base64')
FROM app.assets
WHERE asset_id = 42; | View examples |
| Measure stored bytes | SELECT octet_length(body)
FROM app.assets
WHERE asset_id = 42; | View examples |
| Hash binary content | SELECT encode(digest(body, 'sha256'), 'hex')
FROM app.assets
WHERE asset_id = 42; | View examples |
| Inspect datum storage | SELECT pg_column_size(body), octet_length(body)
FROM app.assets
WHERE asset_id = 42; | View examples |
| Set TOAST storage policy | ALTER TABLE app.assets ALTER COLUMN body
SET STORAGE EXTERNAL; | View examples |
| Create a large object | SELECT lo_from_bytea(0, decode('89504e47', 'hex')); | View examples |
| Read a large-object range | SELECT lo_get(content_oid, 0, 4096)
FROM app.large_assets
WHERE asset_id = 42; | View examples |
| Write a large-object range | SELECT lo_put(content_oid, 4096, decode('00010203', 'hex'))
FROM app.large_assets
WHERE asset_id = 42; | View examples |
| Grant large-object read access | GRANT SELECT ON LARGE OBJECT 24680 TO asset_reader; | View examples |
| Delete a large object | SELECT lo_unlink(24680); | View examples |
| List large-object metadata | SELECT oid, lomowner::regrole
FROM pg_largeobject_metadata
ORDER BY oid; | View examples |
Modeling and Data Types · 16 commands
PostgreSQL Collations and Locale Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Inventory available collations | SELECT collname,
collprovider,
collisdeterministic,
collversion
FROM pg_collation
ORDER BY collname; | View examples |
| Decode provider metadata | SELECT collname,
CASE collprovider WHEN 'b' THEN 'builtin' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END
FROM pg_collation; | View examples |
| Create a deterministic ICU collation | CREATE COLLATION app.en_sort (provider = icu, locale = 'en-US'); | View examples |
| Create a builtin collation alias | CREATE COLLATION app.unicode_fast (provider = builtin, locale = 'PG_UNICODE_FAST'); | View examples |
| Copy a known collation | CREATE COLLATION app.bytewise FROM pg_catalog."C"; | View examples |
| Create case-insensitive comparison | CREATE COLLATION app.case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false); | View examples |
| Ignore accent differences | CREATE COLLATION app.base_letter (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false); | View examples |
| Set a column collation | display_name text COLLATE app.en_sort NOT NULL | View examples |
| Override one expression | SELECT display_name
FROM app.people
ORDER BY display_name COLLATE app.en_sort; | View examples |
| Resolve mixed-collation input | SELECT left_text COLLATE app.en_sort < right_text COLLATE app.en_sort
FROM app.comparisons; | View examples |
| Enforce insensitive uniqueness | CREATE UNIQUE INDEX users_name_ci_key
ON app.users (name COLLATE app.case_insensitive); | View examples |
| Index a specific sort order | CREATE INDEX people_name_en_idx
ON app.people (display_name COLLATE app.en_sort); | View examples |
| Detect collation version drift | SELECT collname
FROM pg_collation
WHERE collversion IS DISTINCT
FROM pg_collation_actual_version(oid); | View examples |
| Rebuild collation-dependent indexes | REINDEX INDEX CONCURRENTLY app.people_name_en_idx; | View examples |
| Record the current provider version | ALTER COLLATION app.en_sort REFRESH VERSION; | View examples |
| Delegate collation creation | GRANT USAGE, CREATE ON SCHEMA app TO locale_admin; | View examples |
Modeling and Data Types · 17 commands
PostgreSQL Custom Types, Domains, Enums, and Composites Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a constrained domain | CREATE DOMAIN app.email_address AS text CONSTRAINT email_nonempty CHECK (btrim(VALUE) <> ''); | View examples |
| Use a domain in a table | email app.email_address NOT NULL | View examples |
| Stage a domain check | ALTER DOMAIN app.email_address ADD CONSTRAINT email_length CHECK (octet_length(VALUE) <= 320) NOT VALID; | View examples |
| Validate a domain check | ALTER DOMAIN app.email_address VALIDATE CONSTRAINT email_length; | View examples |
| Create an enum type | CREATE TYPE app.order_state AS ENUM ('pending', 'paid', 'shipped', 'cancelled'); | View examples |
| Append an enum label | ALTER TYPE app.order_state ADD VALUE IF NOT EXISTS 'returned'; | View examples |
| Position an enum label | ALTER TYPE app.order_state ADD VALUE 'processing' AFTER 'pending'; | View examples |
| Rename an enum label | ALTER TYPE app.order_state RENAME VALUE 'shipped' TO 'dispatched'; | View examples |
| Order by enum semantics | SELECT id, state FROM app.orders ORDER BY state, id; | View examples |
| Create a composite type | CREATE TYPE app.money_amount AS (amount numeric(18,2), currency_code text); | View examples |
| Construct a typed composite | ROW(49.90, 'USD')::app.money_amount | View examples |
| Read a composite field | SELECT (price).amount, (price).currency_code
FROM app.products; | View examples |
| Add a composite attribute | ALTER TYPE app.money_amount ADD ATTRIBUTE tax_included boolean CASCADE; | View examples |
| Grant type usage | GRANT USAGE
ON TYPE app.order_state,
app.email_address,
app.money_amount TO app_writer; | View examples |
| Cast text explicitly | SELECT $1::text::app.order_state; | View examples |
| Inventory custom types | SELECT n.nspname, t.typname, t.typtype
FROM pg_type t
JOIN pg_namespace n
ON n.oid = t.typnamespace
WHERE n.nspname = 'app'; | View examples |
| Inspect enum labels | SELECT enumlabel, enumsortorder
FROM pg_enum
WHERE enumtypid = 'app.order_state'::regtype
ORDER BY enumsortorder; | View examples |
Modeling and Data Types · 12 commands
PostgreSQL Dates, Times, and Intervals Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Typed date | DATE '2026-08-12' | View examples |
| Local timestamp | TIMESTAMP '2026-08-12 09:30:00' | View examples |
| Instant with offset | TIMESTAMPTZ '2026-08-12 09:30:00-03:00' | View examples |
| Transaction time | CURRENT_TIMESTAMP | View examples |
| Wall-clock time | clock_timestamp() | View examples |
| Typed duration | INTERVAL '2 days 3 hours' | View examples |
| Add a duration | started_at + INTERVAL '30 minutes' | View examples |
| Render in a zone | occurred_at AT TIME ZONE 'America/Sao_Paulo' | View examples |
| Truncate to a unit | date_trunc('month', occurred_at, 'UTC') | View examples |
| Extract a field | EXTRACT(ISODOW FROM occurred_at) | View examples |
| Half-open time window | WHERE occurred_at >= $1 AND occurred_at < $2 | View examples |
| Format for display | to_char(occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') | View examples |
Modeling and Data Types · 14 commands
PostgreSQL Identity, Generated Columns, and Sequences Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Protect an identity column | id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY | View examples |
| Allow identity overrides | id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY | View examples |
| Configure the identity sequence | GENERATED ALWAYS AS IDENTITY (START
WITH 1000 INCREMENT BY 1 CACHE 20
) | View examples |
| Import explicit identity values | INSERT INTO app.accounts OVERRIDING SYSTEM VALUE (id, name)
VALUES (42, 'Ada'); | View examples |
| Regenerate imported identities | INSERT INTO app.accounts OVERRIDING USER VALUE
SELECT *
FROM staging.accounts; | View examples |
| Store a generated value | total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED | View examples |
| Compute a virtual value | full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL | View examples |
| Allocate a sequence value | SELECT nextval('app.ticket_number_seq'::regclass); | View examples |
| Read this session's value | SELECT currval('app.ticket_number_seq'::regclass); | View examples |
| Create an owned sequence | CREATE SEQUENCE app.ticket_number_seq AS bigint CACHE 20 OWNED BY app.tickets.number; | View examples |
| Grant allocation access | GRANT USAGE
ON SEQUENCE app.ticket_number_seq TO app_writer; | View examples |
| Find an attached sequence | SELECT pg_get_serial_sequence('app.accounts', 'id'); | View examples |
| Plan a sequence repair | SELECT setval(pg_get_serial_sequence('app.accounts','id'), max(id), true)
FROM app.accounts; | View examples |
| Inspect generated attributes | SELECT column_name,
is_identity,
identity_generation,
is_generated
FROM information_schema.columns
WHERE table_schema = 'app'; | View examples |
Modeling and Data Types · 12 commands
PostgreSQL JSON and JSONB Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create JSONB | '{"status":"open"}'::jsonb | View examples |
| Read JSON field | payload -> 'customer' | View examples |
| Read text field | payload ->> 'status' | View examples |
| Read a nested path | payload #>> '{customer,email}' | View examples |
| Test containment | payload @> '{"status":"open"}'::jsonb | View examples |
| Test a key | payload ? 'status' | View examples |
| Match a JSON path | payload @? '$.items[*] ? (@.price > 100)' | View examples |
| Build an object | jsonb_build_object('id', id, 'name', name) | View examples |
| Aggregate JSON objects | jsonb_agg(jsonb_build_object('id', id) ORDER BY id) | View examples |
| Set a nested value | jsonb_set(payload, '{status}', '"closed"'::jsonb) | View examples |
| Expand an array | CROSS JOIN LATERAL jsonb_array_elements(payload -> 'items') AS item | View examples |
| Index JSONB operations | CREATE INDEX events_payload_gin_idx
ON events
USING GIN (payload); | View examples |
Modeling and Data Types · 16 commands
PostgreSQL Table Constraints Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a table | CREATE TABLE accounts (id bigint PRIMARY KEY, email text NOT NULL); | View examples |
| Generate identifiers | id bigint GENERATED ALWAYS AS IDENTITY | View examples |
| Require a value | name text NOT NULL | View examples |
| Supply a default | created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP | View examples |
| Validate a condition | CONSTRAINT products_price_nonnegative CHECK (price >= 0) | View examples |
| Declare a primary key | CONSTRAINT accounts_pkey PRIMARY KEY (id) | View examples |
| Use a composite key | PRIMARY KEY (order_id, product_id) | View examples |
| Require uniqueness | CONSTRAINT accounts_email_key UNIQUE (email) | View examples |
| Reference another table | FOREIGN KEY (customer_id) REFERENCES customers (id) | View examples |
| Delete dependent rows | FOREIGN KEY (order_id) REFERENCES orders (id)
ON DELETE CASCADE | View examples |
| Detach on deletion | FOREIGN KEY (manager_id) REFERENCES employees (id)
ON DELETE
SET NULL | View examples |
| Name a rule | CONSTRAINT quantity_positive CHECK (quantity > 0) | View examples |
| Add a constraint | ALTER TABLE products ADD CONSTRAINT products_sku_key UNIQUE (sku); | View examples |
| Defer existing-row validation | ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID; | View examples |
| Validate existing rows | ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check; | View examples |
| Remove a constraint | ALTER TABLE products DROP CONSTRAINT products_sku_key; | View examples |
Modeling and Data Types · 18 commands
PostgreSQL Temporary and Unlogged Tables Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a session temporary table | CREATE TEMP TABLE work_items (item_id bigint PRIMARY KEY, payload jsonb NOT NULL); | View examples |
| Preserve rows across commits | CREATE TEMP TABLE work_items (item_id bigint)
ON COMMIT PRESERVE ROWS; | View examples |
| Clear rows after each commit | CREATE TEMP TABLE work_items (item_id bigint)
ON COMMIT DELETE ROWS; | View examples |
| Drop the table at commit | CREATE TEMP TABLE work_items (item_id bigint)
ON COMMIT DROP; | View examples |
| Materialize a transaction-scoped result | CREATE TEMP TABLE recent_orders
ON COMMIT DROP AS
SELECT order_id, total
FROM app.orders
WHERE created_at >= CURRENT_DATE; | View examples |
| Copy a table shape | CREATE TEMP TABLE staged_orders (LIKE app.orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS); | View examples |
| Index a populated temporary table | CREATE INDEX work_items_customer_idx
ON work_items (customer_id); | View examples |
| Collect temporary-table statistics | ANALYZE work_items; | View examples |
| Bypass temporary name shadowing | SELECT order_id FROM app.orders; | View examples |
| Restrict temporary-table creation | REVOKE TEMPORARY ON DATABASE appdb FROM PUBLIC; | View examples |
| Delegate temporary-table creation | GRANT TEMPORARY ON DATABASE appdb TO batch_worker; | View examples |
| Budget session-local temp buffers | SET temp_buffers = '64MB'; | View examples |
| Create a shared unlogged table | CREATE UNLOGGED TABLE app.ingest_buffer (event_id bigint PRIMARY KEY, payload jsonb NOT NULL); | View examples |
| Materialize an unlogged result | CREATE UNLOGGED TABLE app.daily_rollup AS
SELECT account_id, sum(amount) AS total
FROM app.ledger
GROUP BY account_id; | View examples |
| Promote data to crash-safe storage | ALTER TABLE app.ingest_buffer SET LOGGED; | View examples |
| Convert rebuildable data to unlogged | ALTER TABLE app.daily_rollup SET UNLOGGED; | View examples |
| Audit relation persistence | SELECT n.nspname, c.relname, c.relpersistence
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relkind = 'r'; | View examples |
| Preserve unlogged data in a logical dump | pg_dump --format=custom --file=appdb.dump appdb | View examples |