ROW_NUMBER_GENERATE

Generate a dense, 1-based row number for each row in the result set.

Syntax

ROW_NUMBER_GENERATE()

Description

## Overview Returns a contiguous 1-based integer row number for each row in the result set, based on the natural scan order of the query. Unlike the ROW_NUMBER() window function, this function does not require an OVER clause and is cheaper when any dense ordering is acceptable. Use this when you need sequential numbers for pagination, diagnostics, or ad-hoc identifiers within a single query. ## Behavior - Returns a BIGINT row number starting at 1. - Numbers are contiguous and dense within the result set. - Order reflects the physical scan order of the query, not any logical order. - Re-running the query may produce a different ordering. - Never returns NULL. - Side effect free. ## Compatibility - PG-compat helper. For ordered numbering, prefer the standard `ROW_NUMBER() OVER (ORDER BY ...)` window function.

Examples

-- Add 1-based row numbers
SELECT ROW_NUMBER_GENERATE() AS rn, name
FROM retail.catalog.products;

-- Implement pagination without a window function
SELECT * FROM (
  SELECT ROW_NUMBER_GENERATE() AS rn, *
  FROM obs.catalog.events
) t
WHERE rn BETWEEN 11 AND 20;

-- Combine with other columns for a stable projection id
SELECT ROW_NUMBER_GENERATE() AS seq, name, status
FROM ops.catalog.tasks;

-- Alternative form using the ROW_NUMBER window function for ordered numbering
SELECT ROW_NUMBER() OVER (ORDER BY name) AS rn, name
FROM retail.catalog.products;

Pitfalls

• Numbers reflect scan order, not logical order. Two executions of the same query can assign different row numbers to the same row when the underlying data is partitioned or reshuffled.
• The dense numbering is per query, not per session or table. Do not persist ROW_NUMBER_GENERATE values as primary keys.
• For ordered numbering, the ROW_NUMBER() window function with an explicit ORDER BY is the right choice. ROW_NUMBER_GENERATE does not accept an ordering clause.
• Without an outer ORDER BY, the row numbers in the returned rowset can still appear out of order due to parallelism; add an ORDER BY on the numbering column if you need sorted output.

See Also

• monotonically_increasing_id
• generate_series