Convert pg_constraint.conkey array to same-order array of column names

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Convert pg_constraint.conkey array to same-order array of column names
Date: 2023-03-23 11:12:53
Message-ID: CAFCRh-_RY7x0AE0=j-Vu5i5f+FQ=x6ze+4wp1suyAZ=nPQVS1A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message vignesh C 2023-03-23 13:36:32 Re: Support logical replication of DDLs
Previous Message Kyotaro Horiguchi 2023-03-23 08:17:42 Re: Logical replication fails when adding multiple replicas