pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Convert pg_constraint.conkey array to same-order array of column names
8+ messages / 3 participants
[nested] [flat]

* Convert pg_constraint.conkey array to same-order array of column names
@ 2023-03-23 11:12 Dominique Devienne <ddevienne@gmail.com>
  2023-03-23 15:20 ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 10:51 ` Re: Convert pg_constraint.conkey array to same-order array of column names Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 2 replies; 8+ messages in thread

From: Dominique Devienne @ 2023-03-23 11:12 UTC (permalink / raw)
  To: 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?

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-23 15:20 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 10:28   ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-03-23 15:20 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; pgsql-general

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

A before coffee solution:

WITH ck AS (
     SELECT
         conrelid,
         unnest(conkey) AS ky
     FROM
         pg_constraint
     WHERE
         conrelid = 'cell_per'::regclass
)
SELECT
     array_agg(attname)
FROM
     ck
JOIN
     pg_attribute
ON
     attnum = ck.ky AND ck.conrelid = pg_attribute.attrelid;

array_agg
-----------------------------
{category,line_id,category}


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-23 15:20 ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-03-24 10:28   ` Dominique Devienne <ddevienne@gmail.com>
  2023-03-24 16:40     ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Dominique Devienne @ 2023-03-24 10:28 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general

On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver <adrian.klaver@aklaver.com>
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 responding.


> WITH ck AS (
>      SELECT
>          conrelid,
>          unnest(conkey) AS ky
>      FROM
>          pg_constraint
>      WHERE
>          conrelid = 'cell_per'::regclass
> )
>

This part surprised me. I didn't know a table-valued function could be used
like this on the select-clause.

Both queries below yield the same rows for me, in the same order:

=> select conname, unnest(conkey), conrelid::regclass::text from
pg_constraint where conrelid::regclass::text like ... and
cardinality(conkey) = 8;
=> select conname, key.value, conrelid::regclass::text from pg_constraint
cross join lateral unnest(conkey) as key(value) where
conrelid::regclass::text like ... and cardinality(conkey) = 8;

So your compact form is equivalent to the second form?
What about the order? Is it guaranteed?
I was "raised" on the "order is unspecified w/o an order-by-clause". Why
would be it be different here?
In our case, the query is more complex, with joins on pg_namespace,
pg_class, and pg_attribute, on
all constraints of a schema, and the order came out wrong w/o adding WITH
ORDINALITY and ordering on it.

Thus I worry the order is plan-dependent, and not guaranteed. Am I wrong to
worry?
The form you provide seems no different from our old form, to my non-expert
eye. --DD

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-23 15:20 ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 10:28   ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-24 16:40     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 16:44       ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-03-24 16:40 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general

On 3/24/23 03:28, Dominique Devienne wrote:
> On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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 responding.
> 
>     WITH ck AS (
>           SELECT
>               conrelid,
>               unnest(conkey) AS ky
>           FROM
>               pg_constraint
>           WHERE
>               conrelid = 'cell_per'::regclass
>     )
> 
> 
> This part surprised me. I didn't know a table-valued function could be 
> used like this on the select-clause.
> 
> Both queries below yield the same rows for me, in the same order:
> 
> => select conname, unnest(conkey), conrelid::regclass::text from 
> pg_constraint where conrelid::regclass::text like ... and 
> cardinality(conkey) = 8;
> => select conname, key.value, conrelid::regclass::text from 
> pg_constraint cross join lateral unnest(conkey) as key(value) where 
> conrelid::regclass::text like ... and cardinality(conkey) = 8;
> 
> So your compact form is equivalent to the second form?
> What about the order? Is it guaranteed?
> I was "raised" on the "order is unspecified w/o an order-by-clause". Why 
> would be it be different here?
> In our case, the query is more complex, with joins on pg_namespace, 
> pg_class, and pg_attribute, on
> all constraints of a schema, and the order came out wrong w/o adding 
> WITH ORDINALITY and ordering on it.

Your original question was:

"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?"

That is what I showed as a simple example.

> 
> Thus I worry the order is plan-dependent, and not guaranteed. Am I wrong 
> to worry?
> The form you provide seems no different from our old form, to my 
> non-expert eye. --DD

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-23 15:20 ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 10:28   ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-24 16:40     ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-03-24 16:44       ` Dominique Devienne <ddevienne@gmail.com>
  2023-03-24 17:22         ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Dominique Devienne @ 2023-03-24 16:44 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general

On Fri, Mar 24, 2023 at 5:40 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 3/24/23 03:28, Dominique Devienne wrote:
> > On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver <adrian.klaver@aklaver.com
> > <mailto:adrian.klaver@aklaver.com>> wrote:
> Your original question was:
>
> "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?"
>
> That is what I showed as a simple example.
>

Sorry, didn't mean to offend you.

My post started with "preserving order of the columns",
but I probably didn't make it clear enough preserving the source array's
order was also a requirement.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-23 15:20 ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 10:28   ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-24 16:40     ` Re: Convert pg_constraint.conkey array to same-order array of column names Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-24 16:44       ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-24 17:22         ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2023-03-24 17:22 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general

On 3/24/23 9:44 AM, Dominique Devienne wrote:
> On Fri, Mar 24, 2023 at 5:40 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 3/24/23 03:28, Dominique Devienne wrote:
>      > On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>     Your original question was:
> 
>     "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?"
> 
>     That is what I showed as a simple example.
> 
> 
> Sorry, didn't mean to offend you.

There was no offense taken, it was about confusion as to what was being 
asked?

> 
> My post started with "preserving order of the columns",
> but I probably didn't make it clear enough preserving the source array's 
> order was also a requirement.


-- 
Adrian Klaver
adrian.klaver@aklaver.com





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-24 10:51 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  2023-03-24 12:07   ` Re: Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Alvaro Herrera @ 2023-03-24 10:51 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: 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)





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Convert pg_constraint.conkey array to same-order array of column names
  2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
  2023-03-24 10:51 ` Re: Convert pg_constraint.conkey array to same-order array of column names Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-03-24 12:07   ` Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Dominique Devienne @ 2023-03-24 12:07 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-general

On Fri, Mar 24, 2023 at 11:51 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
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 or name.
>

Thanks. This lead me to a better search, yielding two informative results:
https://stackoverflow.com/questions/60877627/perform-unnest-inner-join-and-then-array-agg-as-part-of...
http://amyszczepanski.com/2019/03/25/unnest-and-array-agg-respecting-order.html

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-03-24 17:22 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-23 11:12 Convert pg_constraint.conkey array to same-order array of column names Dominique Devienne <ddevienne@gmail.com>
2023-03-23 15:20 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-24 10:28   ` Dominique Devienne <ddevienne@gmail.com>
2023-03-24 16:40     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-24 16:44       ` Dominique Devienne <ddevienne@gmail.com>
2023-03-24 17:22         ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-24 10:51 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-24 12:07   ` Dominique Devienne <ddevienne@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox