---
title: "Postgres Functions Cheatsheet"
slug: "postgres-functions"
category: "cheatsheets"
tags: ["cheatsheets", "postgres", "sql", "reference", "backend", "database"]
status: "stable"
last_updated: 2026-05-14
summary: "Postgres aggregate, window, date/time, and JSON functions with examples; the built-ins worth memorizing for analytical queries and JSON manipulation."
related:
  [
    "[[backend/postgres]]",
    "[[backend/postgres-jsonb]]",
    "[[backend/postgres-indexes]]",
    "[[cheatsheets/postgres-explain]]",
    "[[backend/postgres-full-text-search]]",
    "[[cheatsheets/index|Cheatsheets]]",
  ]
---

## 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 [[cheatsheets/postgres-explain]]. For full JSONB operator coverage, see [[backend/postgres-jsonb]].

All examples assume Postgres 15+.

## Aggregate functions

Aggregates collapse groups of rows into a single value.

| Function | What 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. |

```sql
-- 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:
```sql
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.

| Function | What 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). |

```sql
-- 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

| Function | What it returns |
| --- | --- |
| `now()` | Current timestamp with time zone. |
| `current_date` | Current date only (no time). |
| `current_timestamp` | Alias 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. |

```sql
-- 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 operator | What 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 || '{"extra":1}'::jsonb` | Merge/concatenate two `jsonb` values. |
| `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. |

```sql
-- 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.

## Related

- [[backend/postgres]]
- [[backend/postgres-jsonb]]
- [[backend/postgres-indexes]]
- [[cheatsheets/postgres-explain]]
- [[backend/postgres-full-text-search]]
- [[cheatsheets/index|Cheatsheets]]