WITH ORDINALITY

WITH ORDINALITY is a SQL statement in the PostgreSQL Specific category. PostgreSQL: appends a row-number column to set-returning functions. Useful for preserving order. The syntax is SELECT * FROM unnest(array_col) WITH ORDINALITY AS elem(value, ordinal);. It returns rows with ordinal positions. A typical example: SELECT * FROM unnest(ARRAY['a', 'b', 'c']) WITH ORDINALITY AS t(value, n); -- value | n -- ---- -- a | 1 -- b | 2 -- c | 3 SELECT * FROM regexp_split_to_table('a,b,c', ',') WITH ORDINALITY AS t(val, pos); -- Preserves original position index A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server). A close relative is ARRAY, which postgreSQL supports array columns and array operations. Can be multi-dimensional. More about this category: PostgreSQL-specific features — SERIAL, RETURNING, ARRAY, JSONB, UPSERT.