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

On 3/24/23 9:44 AM, Dominique Devienne wrote: On Fri, Mar 24, 2023 at 5:40 PM Adrian Klaver > wrote: On 3/24/23 03:28, Dominique Devienne wrote: > On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver mailto:adrian.kla...@aklaver.com> >

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

On Fri, Mar 24, 2023 at 5:40 PM Adrian Klaver wrote: > On 3/24/23 03:28, Dominique Devienne wrote: > > On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver > > wrote: > Your original question was: > > "But I'm wondering about getting 1 row per constraint instead, > and

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

On 3/24/23 03:28, Dominique Devienne wrote: On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver > wrote: On 3/23/23 04:12, Dominique Devienne wrote: > CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS cols(value, rank) >   ORDER BY cols.rank

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

On Fri, Mar 24, 2023 at 11:51 AM Alvaro Herrera wrote: > On 2023-Mar-23, Dominique Devienne wrote: > > 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

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

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 > ...

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

On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver wrote: > On 3/23/23 04:12, Dominique Devienne wrote: > > CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS cols(value, > rank) > > ORDER BY cols.rank > A before coffee solution: > Thanks for answering Adrian. And sorry for the delay in res

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

On 3/23/23 04:12, 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 LA