LLM Best Practices

Postgres Functions Cheatsheet

5 min read

View markdown source

Overview

Postgres ships hundreds of built-in functions. This card covers the four groups you reach for most often: aggregates, window functions, date/time, and JSON. For query-plan analysis, see postgres-explain. For full JSONB operator coverage, see postgres-jsonb.

All examples assume Postgres 15+.

Aggregate functions

Aggregates collapse groups of rows into a single value.

FunctionWhat it returns
count(*)Total rows in the group.
count(col)Rows where col is not null.
count(DISTINCT col)Distinct non-null values of col.
sum(col)Sum of the group.
avg(col)Average; returns numeric.
min(col) / max(col)Minimum or maximum value.
array_agg(col)Collect values into a Postgres array.
array_agg(col ORDER BY col)Ordered array.
string_agg(col, ',')Concatenate with a delimiter.
string_agg(col, ',' ORDER BY col)Ordered concatenation.
bool_and(expr) / bool_or(expr)True if all / any rows satisfy the expression.
json_agg(row)Aggregate rows into a JSON array.
jsonb_agg(row)Same as json_agg but returns jsonb.
jsonb_object_agg(key, value)Build a jsonb object from key-value pairs.
-- Tags per post as a comma-separated string
SELECT post_id, string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;
 
-- All user IDs per role as an array
SELECT role, array_agg(id ORDER BY id) AS user_ids
FROM users
GROUP BY role;

array_agg and string_agg both accept FILTER (WHERE ...) to aggregate a subset:

SELECT count(*) FILTER (WHERE status = 'active') AS active_count FROM users;

Window functions

Window functions compute a value across a set of rows related to the current row, without collapsing the rows.

FunctionWhat it computes
row_number()Sequential integer per row within the partition.
rank()Rank with gaps on ties (1, 1, 3).
dense_rank()Rank without gaps (1, 1, 2).
ntile(n)Divide rows into n equal buckets.
lag(col, offset, default)Value of col from offset rows before.
lead(col, offset, default)Value from offset rows after.
first_value(col)First value in the window frame.
last_value(col)Last value in the window frame.
sum(col) OVER (...)Running sum.
avg(col) OVER (...)Running average.
percent_rank()Relative rank as a fraction (0 to 1).
cume_dist()Cumulative distribution (fraction of rows at or before).
-- Rank users by score within each cohort
SELECT name, cohort, score,
  rank() OVER (PARTITION BY cohort ORDER BY score DESC) AS cohort_rank
FROM users;
 
-- Day-over-day change
SELECT day, revenue,
  revenue - lag(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_sales;
 
-- Running total
SELECT order_date, amount,
  sum(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

Use PARTITION BY to scope the window; omit it to apply across the entire result set.

Date and time functions

FunctionWhat it returns
now()Current timestamp with time zone.
current_dateCurrent date only (no time).
current_timestampAlias for now().
clock_timestamp()Wall-clock time; changes within a transaction.
age(ts)Interval between ts and now.
age(ts1, ts2)Interval between two timestamps.
date_trunc('month', ts)Truncate to start of the month.
date_trunc('week', ts)Truncate to start of the week (Monday).
extract(epoch FROM ts)Unix timestamp as float.
extract(year FROM ts)Year as integer.
date_part('hour', ts)Same as extract; numeric.
ts + INTERVAL '7 days'Add an interval.
ts AT TIME ZONE 'UTC'Convert to UTC.
make_interval(days => 3)Build an interval from named parts.
-- Active users in the last 30 days
SELECT count(*) FROM users WHERE last_active > now() - INTERVAL '30 days';
 
-- Monthly revenue
SELECT date_trunc('month', created_at) AS month, sum(amount)
FROM orders
GROUP BY 1 ORDER BY 1;

Store timestamps as timestamptz (with time zone), not timestamp. The zone offsets the value on read; all comparisons remain correct.

JSON and JSONB functions

Function or operatorWhat it does
col->'key'Get JSON object field as json/jsonb.
col->>'key'Get JSON object field as text.
col->'key'->'nested'Chain for nested access.
col #> '{a,b}'Access nested path as json/jsonb.
col #>> '{a,b}'Access nested path as text.
jsonb_set(col, '{key}', '"value"')Set a field; returns updated jsonb.
jsonb_set(col, '{a,b}', '42')Set a nested field.
`col
col - 'key'Remove a key from jsonb.
jsonb_path_query(col, '$.items[*].id')JSONPath query returning a set.
jsonb_path_query_first(col, '$.items[0]')First JSONPath match.
jsonb_path_exists(col, '$.status == "active"')True if path matches.
jsonb_each(col)Expand top-level keys as rows.
jsonb_array_elements(col)Expand a JSON array as rows.
jsonb_strip_nulls(col)Remove keys with null values.
-- Filter rows by a nested JSONB field
SELECT id FROM events WHERE data->>'type' = 'click';
 
-- Update a nested field without overwriting the whole column
UPDATE configs
SET settings = jsonb_set(settings, '{theme,color}', '"dark"')
WHERE user_id = 42;
 
-- Expand an array of tags and count occurrences
SELECT tag, count(*) FROM posts, jsonb_array_elements_text(tags_jsonb) AS tag GROUP BY 1 ORDER BY 2 DESC;

Common gotchas

  • avg() returns numeric, not the input type. Explicit casting to float8 is sometimes needed when the result feeds a float calculation.
  • last_value() in a window function uses the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), so it returns the current row’s value, not the partition’s last. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last.
  • extract(epoch FROM interval) and extract(epoch FROM timestamp) have different semantics. Epoch of an interval returns total seconds; epoch of a timestamp returns Unix time.
  • now() is stable within a transaction; it returns the transaction start time. Use clock_timestamp() to get the actual wall-clock time at each call.
  • -> returns json/jsonb; -->> returns text. Comparing a json column with -> to a string with = fails unless you use ->>.
  • jsonb_set creates the key if it does not exist but requires the path to already exist at all levels above. Use jsonb_set(col, '{a,b}', '1', true) to create missing intermediate keys.

Graph View

Backlinks