Concept · SQL & Querying

STRING_AGG (and array_agg) without an internal ORDER BY concatenate in arbitrary order — the output silently encodes scan order.

Aggregation order is not promised — the engine feeds rows to the aggregate in whatever order the plan produced, and for SUM that's invisible, but for order-sensitive aggregates (string concatenation, array_agg) the output silently encodes scan order. The fix is the aggregate-internal ORDER BY with a unique tiebreaker; the habit is a question: does this aggregate's output depend on row order?

See it break

floats

f
a
b
c

The trap

SELECT
  string_agg(
    f,
    ' -> '
    ) AS lineup
FROM
  floats;

The fix

SELECT
  string_agg(
    f,
    ' -> '
    ORDER BY
      f
  ) AS lineup
FROM
  floats;

The parade lineup banner flickered between refreshes though nothing wrote — string_agg without an internal ORDER BY concatenates in whatever order the plan produced, so the same rows yield different strings; string_agg(f, ... ORDER BY f) is stable.

The rule

string_agg(x, sep ORDER BY key, tiebreak); same for array_agg; audit every order-sensitive aggregate in anything diffed or cached.

Shows up elsewhere

'Latest' without a tiebreaker LIMIT without ORDER BY

Try one →Prove it in practice →