public inbox for [email protected]
help / color / mirror / Atom feedRe: Where the info is stored
14+ messages / 4 participants
[nested] [flat]
* Re: Where the info is stored
@ 2026-02-17 00:49 David G. Johnston <[email protected]>
2026-02-17 14:59 ` Re: Where the info is stored Greg Sabino Mullane <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: David G. Johnston @ 2026-02-17 00:49 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Monday, February 16, 2026, Igor Korot <[email protected]> wrote:
>
> Where are included columns
>
pg_attribute, though you need info from pg_index to interpret the contents.
>
> and storage_parameters located?
pg_class.reloptions (pretty sure…)
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
@ 2026-02-17 14:59 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Greg Sabino Mullane @ 2026-02-17 14:59 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
The "echo hidden" flag for psql is very helpful for things like this, as
you can see how it finds out information from the system catalogs for the
various backslash commands
psql -E
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
@ 2026-02-27 16:17 ` David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2026-02-27 16:17 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Please keep replies on-list.
On Mon, Feb 16, 2026 at 5:49 PM David G. Johnston <
[email protected]> wrote:
> On Monday, February 16, 2026, Igor Korot <[email protected]> wrote:
>
>>
>> Where are included columns
>
>
>>
> pg_attribute, though you need info from pg_index to interpret the contents.
>
Specifically:
\set ON_ERROR_STOP on
BEGIN;
CREATE TABLE wip_idx_include_demo (
id integer NOT NULL,
secondary_id integer NOT NULL,
included_payload text,
notes text,
CONSTRAINT wip_idx_include_demo_id_secondary_uq
UNIQUE (id, secondary_id) INCLUDE (included_payload)
);
WITH idx AS (
SELECT i.indexrelid, i.indrelid, i.indnkeyatts, i.indkey::int2[] AS
indkey
FROM pg_index i
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_namespace ns ON ns.oid = ic.relnamespace
WHERE ns.nspname = 'public'
AND ic.relname = 'wip_idx_include_demo_id_secondary_uq'
), ords AS (
SELECT idx.indexrelid,
idx.indrelid,
idx.indnkeyatts,
s.ord,
idx.indkey[s.ord] AS attnum
FROM idx
CROSS JOIN LATERAL generate_subscripts(idx.indkey, 1) AS s(ord)
)
SELECT ns.nspname AS schema_name,
ic.relname AS index_name,
tc.relname AS table_name,
a.attname AS column_name,
CASE WHEN ords.ord < ords.indnkeyatts THEN 'key' ELSE 'include' END AS
column_role,
ords.ord + 1 AS index_position
FROM ords
JOIN pg_class ic ON ic.oid = ords.indexrelid
JOIN pg_namespace ns ON ns.oid = ic.relnamespace
JOIN pg_class tc ON tc.oid = ords.indrelid
JOIN pg_attribute a ON a.attrelid = ords.indrelid
AND a.attnum = ords.attnum
AND NOT a.attisdropped
ORDER BY ords.ord \gx
\d+ wip_idx_include_demo_id_secondary_uq
--given that the above provides the relevant info Greg's suggestion would
also get you a functioning base query.
ROLLBACK;
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
@ 2026-03-01 00:37 ` Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Igor Korot @ 2026-03-01 00:37 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, David,
On Fri, Feb 27, 2026 at 10:18 AM David G. Johnston
<[email protected]> wrote:
>
> Please keep replies on-list.
>
> On Mon, Feb 16, 2026 at 5:49 PM David G. Johnston <[email protected]> wrote:
>>
>> On Monday, February 16, 2026, Igor Korot <[email protected]> wrote:
>>>
>>>
>>> Where are included columns
>>>
>>>
>>
>> pg_attribute, though you need info from pg_index to interpret the contents.
>
>
> Specifically:
>
> \set ON_ERROR_STOP on
>
> BEGIN;
>
> CREATE TABLE wip_idx_include_demo (
> id integer NOT NULL,
> secondary_id integer NOT NULL,
> included_payload text,
> notes text,
> CONSTRAINT wip_idx_include_demo_id_secondary_uq
> UNIQUE (id, secondary_id) INCLUDE (included_payload)
> );
>
> WITH idx AS (
> SELECT i.indexrelid, i.indrelid, i.indnkeyatts, i.indkey::int2[] AS indkey
> FROM pg_index i
> JOIN pg_class ic ON ic.oid = i.indexrelid
> JOIN pg_namespace ns ON ns.oid = ic.relnamespace
> WHERE ns.nspname = 'public'
> AND ic.relname = 'wip_idx_include_demo_id_secondary_uq'
> ), ords AS (
> SELECT idx.indexrelid,
> idx.indrelid,
> idx.indnkeyatts,
> s.ord,
> idx.indkey[s.ord] AS attnum
> FROM idx
> CROSS JOIN LATERAL generate_subscripts(idx.indkey, 1) AS s(ord)
> )
> SELECT ns.nspname AS schema_name,
> ic.relname AS index_name,
> tc.relname AS table_name,
> a.attname AS column_name,
> CASE WHEN ords.ord < ords.indnkeyatts THEN 'key' ELSE 'include' END AS column_role,
> ords.ord + 1 AS index_position
> FROM ords
> JOIN pg_class ic ON ic.oid = ords.indexrelid
> JOIN pg_namespace ns ON ns.oid = ic.relnamespace
> JOIN pg_class tc ON tc.oid = ords.indrelid
> JOIN pg_attribute a ON a.attrelid = ords.indrelid
> AND a.attnum = ords.attnum
> AND NOT a.attisdropped
> ORDER BY ords.ord \gx
>
> \d+ wip_idx_include_demo_id_secondary_uq
> --given that the above provides the relevant info Greg's suggestion would also get you a functioning base query.
>
> ROLLBACK;
Just tried the following:
draft=# CREATE TABLE leagues_new(id serial, name varchar(100),
drafttype smallint, scoringtype smallint, roundvalues smallint,
leaguetype char(5), salary integer, benchplayers smallint, primary
key(id) INCLUDE (drafttype, scoringtype) WITH( fillfactor = 50 ));
CREATE TABLE
draft=#
draft=#
draft=# SELECT co.conname AS name, ( WITH idx AS( SELECT
i.indexrelid, i.indrelid, i.indnkeyatts, i.indkey::int2[] AS indkey
FROM pg_index i, pg_class ic, pg_namespace ns WHERE ic.oid =
i.indexrelid AND ns.oid = ic.relnamespace AND ns.nspname = 'public'
AND ic.relname = 'leagues_new' ), ords AS ( SELECT idx.indexrelid,
idx.indrelid, idx.indnkeyatts, s.ord, idx.indkey[s.ord] AS attnum FROM
idx CROSS JOIN LATERAL generate_subscripts(idx.indkey, 1) AS s(ord) )
SELECT a.attname FROM pg_attribute a, ords WHERE a.attrelid =
ords.indrelid AND a.attnum = ords.attnum AND NOT a.attisdropped AND
ords.ord > ords.indnkeyatts ) AS include, n.nspname AS tablespace,
cl.reloptions AS with FROM pg_constraint co, pg_namespace n, pg_class
cl WHERE co.contype = 'p' AND n.nspname = 'public' AND cl.relname =
'leagues_new' AND cl.oid = co.conrelid AND n.oid = cl.relnamespace;
name | include | tablespace | with
------------------+---------+------------+------
leagues_new_pkey | | public |
(1 row)
draft=#
As you can see only the constraint name and the tablespace are
populated correctly.
I'm trying to get all the info in one hit from the ODBC based
solution, hence the huge query.
Do you see a way of improvement?
Thank you.
>
> David J.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 01:02 ` David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2026-03-01 01:02 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
> FROM pg_constraint co, pg_namespace n, pg_class
>
> As you can see only the constraint name and the tablespace are
> populated correctly.
Constraints don’t have included columns. Only indexes do. You need to
query the index, not the constraint.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
@ 2026-03-01 01:05 ` Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:35 ` Re: Where the info is stored David G. Johnston <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Igor Korot @ 2026-03-01 01:05 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, David,
On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
<[email protected]> wrote:
>
> On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
>>
>> FROM pg_constraint co, pg_namespace n, pg_class
>>
>> As you can see only the constraint name and the tablespace are
>> populated correctly.
>
>
> Constraints don’t have included columns. Only indexes do. You need to query the index, not the constraint.
I literally copied your query into my code and it didn't populated
anything...
Am I missing something?
Thank you.
>
> David J.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 01:34 ` Igor Korot <[email protected]>
2026-03-01 01:42 ` Re: Where the info is stored David G. Johnston <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: Igor Korot @ 2026-03-01 01:34 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
And why there is no WHERE populated?
Thank you.
On Sat, Feb 28, 2026 at 7:05 PM Igor Korot <[email protected]> wrote:
>
> Hi, David,
>
> On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
> <[email protected]> wrote:
> >
> > On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
> >>
> >> FROM pg_constraint co, pg_namespace n, pg_class
> >>
> >> As you can see only the constraint name and the tablespace are
> >> populated correctly.
> >
> >
> > Constraints don’t have included columns. Only indexes do. You need to query the index, not the constraint.
>
> I literally copied your query into my code and it didn't populated
> anything...
>
> Am I missing something?
>
> Thank you.
>
> >
> > David J.
> >
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 01:42 ` David G. Johnston <[email protected]>
2026-03-01 05:34 ` Re: Where the info is stored Igor Korot <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2026-03-01 01:42 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Sat, Feb 28, 2026 at 6:34 PM Igor Korot <[email protected]> wrote:
> And why there is no WHERE populated?
>
> Thank you.
>
> On Sat, Feb 28, 2026 at 7:05 PM Igor Korot <[email protected]> wrote:
> >
> > Hi, David,
> >
> > On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
> > <[email protected]> wrote:
> > >
> > > On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
> > >>
> > >> FROM pg_constraint co, pg_namespace n, pg_class
> > >>
> > >> As you can see only the constraint name and the tablespace are
> > >> populated correctly.
> > >
> > >
> > > Constraints don’t have included columns. Only indexes do. You need
> to query the index, not the constraint.
> >
> > I literally copied your query into my code and it didn't populated
> > anything...
> >
> > Am I missing something?
>
>
I trimmed your query to emphasize/point-out that you were querying
pg_constraint and that doing so to find included columns is doomed to
failure (I suppose it could have been used to find the index, but in this
case it wasn't. I haven't explored that approach.). You should step back
and consider why you thought the fragment I included in my reply, a bare
FROM clause, would somehow be executable since it is in no way a valid
query.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:42 ` Re: Where the info is stored David G. Johnston <[email protected]>
@ 2026-03-01 05:34 ` Igor Korot <[email protected]>
2026-03-01 05:49 ` Re: Where the info is stored David G. Johnston <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Igor Korot @ 2026-03-01 05:34 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, David,
On Sat, Feb 28, 2026 at 7:42 PM David G. Johnston
<[email protected]> wrote:
>
> On Sat, Feb 28, 2026 at 6:34 PM Igor Korot <[email protected]> wrote:
>>
>> And why there is no WHERE populated?
>>
>> Thank you.
>>
>> On Sat, Feb 28, 2026 at 7:05 PM Igor Korot <[email protected]> wrote:
>> >
>> > Hi, David,
>> >
>> > On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
>> > <[email protected]> wrote:
>> > >
>> > > On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
>> > >>
>> > >> FROM pg_constraint co, pg_namespace n, pg_class
>> > >>
>> > >> As you can see only the constraint name and the tablespace are
>> > >> populated correctly.
>> > >
>> > >
>> > > Constraints don’t have included columns. Only indexes do. You need to query the index, not the constraint.
>> >
>> > I literally copied your query into my code and it didn't populated
>> > anything...
>> >
>> > Am I missing something?
>>
>
> I trimmed your query to emphasize/point-out that you were querying pg_constraint and that doing so to find included columns is doomed to failure (I suppose it could have been used to find the index, but in this case it wasn't. I haven't explored that approach.). You should step back and consider why you thought the fragment I included in my reply, a bare FROM clause, would somehow be executable since it is in no way a valid query.
draft=# WITH idx AS( SELECT i.indexrelid, i.indrelid, i.indnkeyatts,
i.indkey::int2[] AS indkey FROM pg_index i, pg_class ic, pg_namespace
ns WHERE ic.oid = i.indexrelid AND ns.oid = ic.relnamespace AND
ns.nspname = 'public' AND ic.relname = 'leagues_new' ), ords AS (
SELECT idx.indexrelid, idx.indrelid, idx.indnkeyatts, s.ord,
idx.indkey[s.ord] AS attnum FROM idx CROSS JOIN LATERAL
generate_subscripts(idx.indkey, 1) AS s(ord) ) SELECT ns.nspname,
ic.relname, tc.relname, a.attname, CASE WHEN ords.ord <
ords.indnkeyatts THEN 'key' ELSE 'include' END, ords.ord + 1 AS
index_position FROM pg_attribute a, ords, pg_class ic, pg_namespace
ns, pg_class tc WHERE a.attrelid = ords.indrelid AND a.attnum =
ords.attnum AND NOT a.attisdropped AND ic.oid = ords.indexrelid AND
ns.oid = ic.relnamespace AND tc.oid = ords.indrelid AND ords.ord >
ords.indnkeyatts;
nspname | relname | relname | attname | case | index_position
---------+---------+---------+---------+------+----------------
(0 rows)
draft=#
This is an exact replica of your query from the first post where you put it.
I only removed AS statements.
As you can see 0 rows are returned.
Thank you.
>
> David J.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:42 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 05:34 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 05:49 ` David G. Johnston <[email protected]>
2026-03-01 07:50 ` Re: Where the info is stored Igor Korot <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2026-03-01 05:49 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
>
> draft=# WITH idx AS( SELECT i.indexrelid, i.indrelid, i.indnkeyatts,
> i.indkey::int2[] AS indkey FROM pg_index i, pg_class ic, pg_namespace
> ns
> WHERE ic.oid = i.indexrelid AND ns.oid = ic.relnamespace AND
> ns.nspname = 'public' AND ic.relname = 'leagues_new' )
>
I doubt your index name is leagues_new. That is probably the table name.
But ic.relname contains index names.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:42 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 05:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 05:49 ` Re: Where the info is stored David G. Johnston <[email protected]>
@ 2026-03-01 07:50 ` Igor Korot <[email protected]>
2026-03-01 07:58 ` Re: Where the info is stored Igor Korot <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Igor Korot @ 2026-03-01 07:50 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, David,
On Sat, Feb 28, 2026 at 11:49 PM David G. Johnston
<[email protected]> wrote:
>
> On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
>>
>>
>> draft=# WITH idx AS( SELECT i.indexrelid, i.indrelid, i.indnkeyatts,
>> i.indkey::int2[] AS indkey FROM pg_index i, pg_class ic, pg_namespace
>> ns
>>
>>
>> WHERE ic.oid = i.indexrelid AND ns.oid = ic.relnamespace AND
>> ns.nspname = 'public' AND ic.relname = 'leagues_new' )
>>
>>
>
> I doubt your index name is leagues_new. That is probably the table name. But ic.relname contains index names.
With some trial and error I think I finally get the query right ;-)
draft=# SELECT c.relname AS name, ixs.tablespace,
-- Get included columns (PostgreSQL 11+)
ARRAY(
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = idx.indrelid
AND a.attnum = ANY(idx.indkey)
AND a.attnum > 0
ORDER BY array_position(idx.indkey, a.attnum)
OFFSET idx.indnkeyatts
) AS included,
c.reloptions AS storage
FROM pg_index idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs
WHERE ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
n.nspname = 'public' AND t.relname = 'leagues_new';
name | tablespace | included | storage
------------------+------------+-------------------------+-----------------
leagues_new_pkey | | {drafttype,scoringtype} | {fillfactor=50}
(1 row)
draft=#
Thank you for sticking with me and sorry for the trouble.
I am getting everything in one DB hit and the query is not that big.
I am only worrying about tablespace filtering but I think I got it
right.
Thx once again.
P.S.: If you see any improvement - please by all means do let me know.
>
> David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:42 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 05:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 05:49 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 07:50 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 07:58 ` Igor Korot <[email protected]>
2026-03-01 10:33 ` Re: Where the info is stored Laurenz Albe <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Igor Korot @ 2026-03-01 07:58 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
It would be interesting to know what to bind ARRAY column
in ODBC code (the type in the SQLBindCol()), but that's a
different topic which is probably OT here.
Thx.
On Sun, Mar 1, 2026 at 1:50 AM Igor Korot <[email protected]> wrote:
>
> Hi, David,
>
> On Sat, Feb 28, 2026 at 11:49 PM David G. Johnston
> <[email protected]> wrote:
> >
> > On Saturday, February 28, 2026, Igor Korot <[email protected]> wrote:
> >>
> >>
> >> draft=# WITH idx AS( SELECT i.indexrelid, i.indrelid, i.indnkeyatts,
> >> i.indkey::int2[] AS indkey FROM pg_index i, pg_class ic, pg_namespace
> >> ns
> >>
> >>
> >> WHERE ic.oid = i.indexrelid AND ns.oid = ic.relnamespace AND
> >> ns.nspname = 'public' AND ic.relname = 'leagues_new' )
> >>
> >>
> >
> > I doubt your index name is leagues_new. That is probably the table name. But ic.relname contains index names.
>
> With some trial and error I think I finally get the query right ;-)
>
> draft=# SELECT c.relname AS name, ixs.tablespace,
> -- Get included columns (PostgreSQL 11+)
> ARRAY(
> SELECT a.attname
> FROM pg_attribute a
> WHERE a.attrelid = idx.indrelid
> AND a.attnum = ANY(idx.indkey)
> AND a.attnum > 0
> ORDER BY array_position(idx.indkey, a.attnum)
> OFFSET idx.indnkeyatts
> ) AS included,
> c.reloptions AS storage
> FROM pg_index idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs
> WHERE ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
> idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
> n.nspname = 'public' AND t.relname = 'leagues_new';
> name | tablespace | included | storage
> ------------------+------------+-------------------------+-----------------
> leagues_new_pkey | | {drafttype,scoringtype} | {fillfactor=50}
> (1 row)
>
> draft=#
>
> Thank you for sticking with me and sorry for the trouble.
>
> I am getting everything in one DB hit and the query is not that big.
>
> I am only worrying about tablespace filtering but I think I got it
> right.
>
> Thx once again.
>
> P.S.: If you see any improvement - please by all means do let me know.
>
>
> >
> > David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:42 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 05:34 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 05:49 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 07:50 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 07:58 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 10:33 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2026-03-01 10:33 UTC (permalink / raw)
To: Igor Korot <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Sun, 2026-03-01 at 01:58 -0600, Igor Korot wrote:
> It would be interesting to know what to bind ARRAY column
> in ODBC code (the type in the SQLBindCol()), but that's a
> different topic which is probably OT here.
I'd suspect that you need to use the string representation of the array
and bind it as string.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Where the info is stored
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-27 16:17 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 00:37 ` Re: Where the info is stored Igor Korot <[email protected]>
2026-03-01 01:02 ` Re: Where the info is stored David G. Johnston <[email protected]>
2026-03-01 01:05 ` Re: Where the info is stored Igor Korot <[email protected]>
@ 2026-03-01 01:35 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2026-03-01 01:35 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Sat, Feb 28, 2026 at 6:05 PM Igor Korot <[email protected]> wrote:
> I literally copied your query into my code and it didn't populated
> anything...
>
Without showing your work that tells me nothing.
> Am I missing something?
>
>
Apparently. I don't have the desire to play 20 questions over email to
figure out what though. Here's the fish.
\set ON_ERROR_STOP on
BEGIN;
CREATE TABLE leagues_new (
id serial,
name varchar(100),
drafttype smallint,
scoringtype smallint,
roundvalues smallint,
leaguetype char(5),
salary integer,
benchplayers smallint,
PRIMARY KEY (id) INCLUDE (drafttype, scoringtype) WITH (fillfactor = 50)
);
WITH idx AS (
SELECT
i.indexrelid,
i.indrelid,
i.indnkeyatts,
i.indkey::int2[] AS indkey
FROM pg_index i
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_namespace ns ON ns.oid = ic.relnamespace
WHERE ns.nspname = 'public'
AND ic.relname = format('%s_pkey', 'leagues_new')
), ords AS (
SELECT
idx.indexrelid,
idx.indrelid,
idx.indnkeyatts,
s.ord,
idx.indkey[s.ord] AS attnum
FROM idx
CROSS JOIN LATERAL generate_subscripts(idx.indkey, 1) AS s(ord)
)
SELECT
ns.nspname AS schema_name,
ic.relname AS index_name,
tc.relname AS table_name,
a.attname AS column_name,
CASE
WHEN ords.ord < ords.indnkeyatts THEN 'key'
ELSE 'include'
END AS column_role,
ords.ord + 1 AS index_position
FROM ords
JOIN pg_class ic ON ic.oid = ords.indexrelid
JOIN pg_namespace ns ON ns.oid = ic.relnamespace
JOIN pg_class tc ON tc.oid = ords.indrelid
JOIN pg_attribute a ON a.attrelid = ords.indrelid
AND a.attnum = ords.attnum
AND NOT a.attisdropped
ORDER BY ords.ord \gx
\d+ leagues_new_pkey
ROLLBACK;
psql --file wip/index-include-scratch.psql
BEGIN
CREATE TABLE
-[ RECORD 1 ]--+-----------------
schema_name | public
index_name | leagues_new_pkey
table_name | leagues_new
column_name | id
column_role | key
index_position | 1
-[ RECORD 2 ]--+-----------------
schema_name | public
index_name | leagues_new_pkey
table_name | leagues_new
column_name | drafttype
column_role | include
index_position | 2
-[ RECORD 3 ]--+-----------------
schema_name | public
index_name | leagues_new_pkey
table_name | leagues_new
column_name | scoringtype
column_role | include
index_position | 3
Index "public.leagues_new_pkey"
Column | Type | Key? | Definition | Storage | Stats target
-------------+----------+------+-------------+---------+--------------
id | integer | yes | id | plain |
drafttype | smallint | no | drafttype | plain |
scoringtype | smallint | no | scoringtype | plain |
primary key, btree, for table "public.leagues_new"
Options: fillfactor=50
ROLLBACK
That is purely AI generated but does produce the correct value and looks
quite reasonable. There may very well be a more idiomatic way to do this,
but I don't write these kinds of queries myself. As previously stated,
feel free to see what psql is sending to the server to produce the second,
\d+, result if you want another query form to consider.
Note, though, the absence of pg_constraint anywhere in this query.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2026-03-01 10:33 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-17 00:49 Re: Where the info is stored David G. Johnston <[email protected]>
2026-02-17 14:59 ` Greg Sabino Mullane <[email protected]>
2026-02-27 16:17 ` David G. Johnston <[email protected]>
2026-03-01 00:37 ` Igor Korot <[email protected]>
2026-03-01 01:02 ` David G. Johnston <[email protected]>
2026-03-01 01:05 ` Igor Korot <[email protected]>
2026-03-01 01:34 ` Igor Korot <[email protected]>
2026-03-01 01:42 ` David G. Johnston <[email protected]>
2026-03-01 05:34 ` Igor Korot <[email protected]>
2026-03-01 05:49 ` David G. Johnston <[email protected]>
2026-03-01 07:50 ` Igor Korot <[email protected]>
2026-03-01 07:58 ` Igor Korot <[email protected]>
2026-03-01 10:33 ` Laurenz Albe <[email protected]>
2026-03-01 01:35 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox