PostgreSQL Cheatsheet

At a glance

Connect

Irodori accepts either a raw url/DSN or structured fields.

Wire-compatible variants change only the port: CockroachDB 26257 (?sslmode=disable for insecure), YugabyteDB YSQL 5433, Redshift 5439.

Query model

• You type SQL; rows come back as a table.
• Rows are capped at max_rows (default 10,000) and flagged truncated: true when more remain. Add LIMIT for large scans.
• Version: select version().
• Multiple statements return multiple result sets.

Essential statements

-- Read with paging
SELECT id, name FROM customers ORDER BY id LIMIT 50 OFFSET 0;

-- Parameters use $1, $2 (positional)
SELECT * FROM orders WHERE tier = $1 AND created_at >= $2;

-- Upsert
INSERT INTO customers (id, name) VALUES (1, 'Ada')
  ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- JSON / JSONB
SELECT data->>'email' AS email FROM events WHERE data @> '{"tier":"gold"}';

-- CTE
WITH recent AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT tier, count(*) FROM recent GROUP BY tier;

• Identifier quoting: ANSI double quotes — "My Col"; escape " by doubling.
• Paging: LIMIT n OFFSET m.

Introspection

-- Schemas, tables, columns via the standard catalog
SELECT table_schema, table_name FROM information_schema.tables
  WHERE table_schema NOT IN ('pg_catalog','information_schema');
SELECT column_name, data_type FROM information_schema.columns
  WHERE table_name = 'customers';

-- psql-style shortcuts (also work through Irodori's editor)
SELECT version();

Irodori’s object browser reads schemas, tables, views, columns, indexes, and constraints through the Postgres information-schema metamodel.

Irodori-specific behavior

• Decoding: NUMERIC→string (exact, via BigDecimal), TIMESTAMPTZ→RFC3339, TIMESTAMP/DATE/TIME→string, JSON/JSONB→object, UUID→string, BYTEA→\xHEX, bool/ints/floats native.
• Arrays decode best-effort to text today (rich array decode is a follow-up).
• CockroachDB omits some OID-typed system columns that tools expect.
• The deep driver/decoding reference is docs/engine-syntax-reference.md.

Gotchas

• Use positional parameters ($1) — not string interpolation.
• now() is transaction-time; use clock_timestamp() for wall-clock.
• Wire-compatible engines share this adapter, so a Postgres-only feature may behave differently on CockroachDB/Yugabyte/Redshift — check the per-engine quirks.

Sources

Generated from knowledge/sources.json:

• postgres-docs-current — https://www.postgresql.org/docs/current/
• postgres-release-notes-current — https://www.postgresql.org/docs/current/release.html