Re: Convert pg_constraint.conkey array to same-order array of column names - Mailing list pgsql-general

On 2023-Mar-23, Dominique Devienne wrote:

> We have a query returning 1 row per constraint column,
> which until recently we didn't realize wasn't preserving order of the
> columns.
> 
> A colleague fixed that, with something like below:
> 
> SELECT ...
> FROM pg_catalog.pg_constraint cnstr
> ...
> CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS cols(value, rank)
>  ORDER BY cols.rank
> 
> But I'm wondering about getting 1 row per constraint instead,
> and fetching an array of column names.

IIRC you can use array_agg(unnest ORDER BY ordinality), grouping by
constraint OID or name.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)