SQL query syntax

Query structure

WITH recent AS (
  SELECT timestamp_utc, account_id, duration_ms
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '7 days'
)
SELECT account_id, COUNT(*) AS events, AVG(duration_ms) AS mean_duration_ms
FROM recent
WHERE account_id IS NOT NULL
GROUP BY account_id
HAVING COUNT(*) >= 10
ORDER BY events DESC, account_id ASC
LIMIT 100;

The clauses serve different purposes:

SELECT also works without a table for expressions such as SELECT 1 + 2 AS result. SQL keywords and built-in function names are case-insensitive. A trailing semicolon is optional for a single query.

Identifiers and aliases

Telemetry preserves identifier case. A field named accountId is distinct from accountid. Use its exact spelling whether quoted or unquoted:

SELECT accountId, "accountId", "response-time"
FROM product_events;

Double-quote identifiers containing spaces, punctuation, or reserved words. Escape an embedded double quote by doubling it. Single quotes always denote string values: 'accountId' is literal text, not a field lookup.

Use table aliases to disambiguate fields in joins. AS names expressions for later use in ordering and grouping. An alias is not a general-purpose variable available everywhere in the same select list; use a CTE or subquery to reuse a calculated expression reliably.

SELECT e.account_id, e.duration_ms AS latency
FROM product_events AS e
ORDER BY latency DESC NULLS LAST;

Nested fields

A nested event object is a structured value, not automatically one flat column per dotted name. Given a data object containing customer and duration_ms, use:

SELECT
  data.customer.id AS customer_id,
  data['duration_ms'] AS duration_ms
FROM product_events
WHERE data.customer.id IS NOT NULL;

Bracket access is useful for field names containing punctuation: data['release-version']. Preserve the case of each field name. To avoid ambiguity between a table qualifier and a struct name, use an explicit table alias and bracket lookup, such as e.data['customer']['id'].

These two expressions are different:

When a path exists in the current table schema but older files lack it, those older values are null. A path that does not exist in the table schema can fail planning. Check the schema before replacing nested access with JSON functions from another database.

Projection and DISTINCT

SELECT * returns all fields. Explicit field lists keep result shapes predictable and avoid reading unneeded data. SELECT DISTINCT a, b removes duplicates across the complete (a, b) pair, not separately per column.

Wildcard exclusion is supported:

SELECT * EXCEPT (raw_payload)
FROM product_events
LIMIT 20;

EXCLUDE is an alternative spelling for this projection operation. It is different from the EXCEPT set operation between queries.

DISTINCT ON

DISTINCT ON (key, ...) keeps one row per key. Its expressions must match the initial ORDER BY expressions. Use the remaining sort expressions to choose which row survives, with a tie-breaker for deterministic results:

SELECT DISTINCT ON (account_id)
  account_id, event_id, timestamp_utc
FROM product_events
ORDER BY account_id, timestamp_utc DESC, event_id DESC;

Inline values and generated rows

VALUES supplies a small inline relation. Give its columns names when using it in a query:

SELECT outcome, severity
FROM (VALUES ('error', 2), ('timeout', 1)) AS labels(outcome, severity);

Each row must have the same number of values, and corresponding columns must have compatible types. This is useful for testing expressions or joining to a small classification table.

UNNEST expands array elements into rows. generate_series includes its stop value; range excludes it. Both accept a step:

SELECT unnest(generate_series(1, 5)) AS n;
SELECT unnest(range(0, 10, 2)) AS even_number;

The functions also have table forms such as SELECT * FROM generate_series(1, 5). Use nested data functions for detailed array behavior, and date/time functions for timestamp calculations. Expanding an array changes the row count; account for that before aggregating or joining.

UNNEST arrays, structs, and map entries

UNNEST(array) expands elements into rows. It works in the select list and as a relation in FROM:

SELECT unnest([10, 20, 30]) AS value;
SELECT * FROM unnest([10, 20, 30]) AS values_table(value);

An empty or typed-null array produces no rows when expanded alone. Null elements inside a non-null array remain null rows. An untyped UNNEST(NULL) is rejected; cast null to an array type when a typed null is intended.

Multiple array expansions in the same select list align elements by position and pad shorter arrays with nulls. Separate relations in FROM produce combinations:

SELECT unnest([1, 2]) AS a, unnest([10]) AS b;
-- Rows: (1, 10), (2, NULL)

SELECT a, b
FROM unnest([1, 2]) AS left_values(a)
CROSS JOIN unnest([10, 20]) AS right_values(b);
-- Four combinations of a and b

The table form also accepts multiple array arguments in one call: SELECT * FROM unnest([1, 2], [10]) AS items(a, b) aligns by position and pads with nulls, producing (1, 10) and (2, NULL). The select-list form accepts exactly one argument per call.

UNNEST(struct) expands the fields into columns rather than multiplying rows. A select-list alias on struct unnest is ignored; use the resulting field names or a relation alias with explicit column names:

SELECT *
FROM unnest(named_struct('label', 'api', 'count', 3)) AS fields(label, count);

Direct UNNEST(map_value) is not supported. Convert a map to an array of entry structs first, then project the key and value fields:

WITH entries AS (
  SELECT unnest(map_entries(map(['POST', 'HEAD'], [41, 33]))) AS entry
)
SELECT entry['key'] AS method, entry['value'] AS requests
FROM entries;

Use nested calls such as unnest(unnest([[1, 2], [3]])) to expand more than one array level. Expansion can change the number of rows entering an aggregate, so choose whether to aggregate before or after it deliberately.

Joins

SELECT e.account_id, a.plan, COUNT(*) AS events
FROM product_events AS e
LEFT JOIN accounts AS a ON e.account_id = a.account_id
WHERE e.timestamp_utc >= now() - INTERVAL '1 day'
GROUP BY e.account_id, a.plan;

USING (account_id) is shorthand when the join key has the same name on both sides. NATURAL JOIN infers keys from shared column names, so schema changes can change its behavior; explicit keys are easier to maintain.

One-to-many and many-to-many joins multiply rows. Aggregate or deduplicate the appropriate side before joining if you need one row per account or event. A right-side predicate in WHERE can remove the null-extended rows of a left join; put a predicate in ON when it belongs to the matching condition.

Lateral subqueries can reference preceding relations through LATERAL, CROSS JOIN LATERAL, or LEFT JOIN LATERAL ... ON. Support depends on whether the correlated expression can be planned; use ordinary joins or CTEs when a correlated shape is rejected.

Grouping and aggregate filters

Select grouping keys and aggregate expressions together. Ordinary selected fields must participate in the grouping. WHERE filters before aggregation; HAVING filters after it.

SELECT
  account_id,
  COUNT(*) AS events,
  COUNT(*) FILTER (WHERE outcome = 'error') AS errors,
  COUNT(DISTINCT user_id) AS users
FROM product_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY account_id
HAVING COUNT(*) >= 20;

GROUPING SETS, ROLLUP, and CUBE compute multiple grouping levels. Use GROUPING(expression) to distinguish subtotal placeholders from actual null group keys. See aggregate functions for ordered aggregates, distinct handling, and approximate statistics.

Window results and QUALIFY

Window expressions retain individual rows while calculating across related rows. QUALIFY filters their results:

SELECT
  account_id,
  event_id,
  timestamp_utc,
  ROW_NUMBER() OVER (
    PARTITION BY account_id
    ORDER BY timestamp_utc DESC, event_id DESC
  ) AS row_number
FROM product_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
QUALIFY row_number = 1;

The final ORDER BY is separate from ordering inside OVER. Add an explicit frame for running calculations or value functions when the default does not express your intent. See window functions.

CTEs and subqueries

A CTE makes query stages readable; it does not promise materialization or caching. Multiple CTEs are separated by commas. A subquery in FROM must be parenthesized and can be aliased.

SELECT account_id, events
FROM (
  SELECT account_id, COUNT(*) AS events
  FROM product_events
  GROUP BY account_id
) AS activity
WHERE events >= 10;

Scalar subqueries must produce one column and at most one row; an empty scalar result is null. Use IN (SELECT ...) for membership and EXISTS (SELECT ...) for existence. EXISTS tests whether any row is returned, independently of the values selected.

SELECT a.account_id
FROM accounts AS a
WHERE EXISTS (
  SELECT 1
  FROM product_events AS e
  WHERE e.account_id = a.account_id
);

Some correlated subquery forms have planning restrictions. An explicit join or aggregation is often a clearer equivalent. NOT IN has a null trap: a null in the subquery can make non-matches unknown. Use NOT EXISTS when expressing the absence of a matching row.

Set operations

UNION combines results and removes duplicate rows; UNION ALL preserves duplicates. INTERSECT retains rows present in both inputs. EXCEPT retains left-input rows absent from the right. Inputs must return the same number of columns with compatible types; correspondence is positional.

SELECT user_id FROM signup_events
UNION
SELECT user_id FROM purchase_events;

Parenthesize query branches when their own sorting or limits matter. Put the final ORDER BY and LIMIT after the combined query when they apply to the overall result.

Ordering and limits

Use ASC or DESC per sort expression, and NULLS FIRST or NULLS LAST for explicit null ordering. Without ORDER BY, row order is not guaranteed. Include a stable tie-breaker if a deterministic top-N result matters.

SELECT event_id, timestamp_utc
FROM product_events
ORDER BY timestamp_utc DESC, event_id DESC
LIMIT 100 OFFSET 100;

Offsets skip rows in the result and can be expensive at large values. A limit does not make an otherwise unbounded aggregation cheap: use a time predicate and select only required fields.