Overview
The built-ins used most for reporting and JSON work: aggregates, date and time, and JSONB. Window functions are on postgres-window-functions; query plans are on postgres-explain; JSONB indexing and operators in depth are in postgres-jsonb. Examples were run on Postgres 16.
Aggregates
Any aggregate accepts FILTER (WHERE ...).
| Function | Returns |
|---|---|
count(*), count(col), count(DISTINCT col) | Rows / non-null values / distinct non-null values. |
sum(col), avg(col), min(col), max(col) | avg of an integer column is numeric; sum of integer is bigint. |
array_agg(col ORDER BY col) | Ordered array. |
string_agg(col, ', ' ORDER BY col) | Ordered, delimited text. |
bool_and(expr), bool_or(expr) | All / any rows true. |
json_agg(row), jsonb_agg(row), jsonb_object_agg(k, v) | JSON array / JSONB array / JSONB object. |
SELECT role, count(*) FILTER (WHERE active) AS active_users,
string_agg(name, ', ' ORDER BY name) AS names
FROM users GROUP BY role;Date and time
Use timestamptz values with date_trunc and interval arithmetic.
| Expression | Returns |
|---|---|
now(), current_timestamp | Transaction start time as timestamptz. |
clock_timestamp() | Actual current time, different on every call. |
date_trunc('month', ts) | Start of the month; 'week' starts on Monday. |
extract(epoch FROM ts) | Unix seconds as numeric; date_part returns double precision. |
age(ts) | Interval from ts to today at midnight (current_date), not to now(). |
age(ts1, ts2) | Interval between two timestamps. |
ts + INTERVAL '7 days', make_interval(days => 3) | Date arithmetic. |
ts AT TIME ZONE 'UTC' | Convert between timestamptz and local timestamp. |
generate_series(start, stop, INTERVAL '1 day') | One row per step. |
to_char(ts, 'YYYY-MM-DD HH24:MI') | Formatted text. |
Store instants as timestamptz; Postgres converts to UTC on write and to the session time zone on read.
JSON and JSONB
Use ->> for text comparison and @> for indexed containment.
| Expression | Does |
|---|---|
col -> 'k', col ->> 'k' | Field as jsonb / as text. |
col #> '{a,b}', col #>> '{a,b}' | Nested path as jsonb / as text. |
col @> '{"k":1}', col ? 'k' | Contains / has top-level key (GIN-indexable). |
col || '{"x":1}', col - 'k' | Merge / delete a key. |
jsonb_set(col, '{a,b}', '42') | Replace or add the last path key. |
jsonb_path_query(col, '$.items[*].id'), jsonb_path_query_first(col, '$.items[0]') | SQL/JSON path query returning rows / the first match. |
col @? '$.a', col @@ '$.a == 1' | Path exists / path predicate. |
jsonb_each(col), jsonb_array_elements(col) | Expand to rows. |
jsonb_build_object('a', 1), jsonb_strip_nulls(col) | Construct / drop null fields. |
Postgres 17 adds SQL-standard JSON_TABLE, JSON_QUERY, JSON_VALUE, and JSON_EXISTS.
SELECT id FROM events WHERE data ->> 'type' = 'click';
UPDATE configs SET settings = jsonb_set(settings, '{theme,color}', '"dark"') WHERE user_id = 42;
SELECT tag, count(*) FROM posts, jsonb_array_elements_text(tags) AS tag GROUP BY 1 ORDER BY 2 DESC;Gotchas
Avoid these; each returns a surprising type or silently skips an update.
| Gotcha | Fix |
|---|---|
extract(epoch FROM interval) is total seconds; extract(epoch FROM timestamptz) is Unix time. | Check the input type before reusing the expression. |
now() is fixed for the whole transaction. | Use clock_timestamp() for elapsed time inside one. |
jsonb_set does not create missing intermediate keys; create_if_missing only affects the last key. | Build the parent first, or set the whole subtree. |
col -> 'k' = 'text' parses the literal as JSON and raises an error. | Use ->> for text comparison, or compare with '"text"'::jsonb. |
avg() of integers is numeric, which can surprise float math. | Cast with ::float8. |
count(col) skips nulls; count(*) does not. | Pick the one you mean. |