Postgres Pro
Downloads
Home   >   mailing lists

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

From Dominique Devienne
Subject Convert pg_constraint.conkey array to same-order array of column names
Date
Msg-id CAFCRh-_RY7x0AE0=j-Vu5i5f+FQ=x6ze+4wp1suyAZ=nPQVS1A@mail.gmail.com
Whole thread Raw
Responses Re: Convert pg_constraint.conkey array to same-order array of column names  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Convert pg_constraint.conkey array to same-order array of column names  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
List pgsql-general
Tree view
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.

So is there a way to "convert" int2[] conkey array into a text[] of those column names?

pgsql-general by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: Logical replication fails when adding multiple replicas
Next
From: Magnus Hagander
Date:
Subject: Re: Postgresql professional certification