pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedConvert 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>
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 15:20 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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-24 10:28 Dominique Devienne <ddevienne@gmail.com>
parent: 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-24 10:51 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: 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-24 12:07 Dominique Devienne <ddevienne@gmail.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
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
* Re: Convert pg_constraint.conkey array to same-order array of column names
@ 2023-03-24 16:40 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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-24 16:44 Dominique Devienne <ddevienne@gmail.com>
parent: 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-24 17:22 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Dominique Devienne <ddevienne@gmail.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
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