Where the info is stored

On Monday, February 16, 2026, Igor Korot <ikorot01@gmail.com> 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.

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

Please keep replies on-list.

On Mon, Feb 16, 2026 at 5:49 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Monday, February 16, 2026, Igor Korot <ikorot01@gmail.com> 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.

Hi, David,

On Fri, Feb 27, 2026 at 10:18 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

Please keep replies on-list.

On Mon, Feb 16, 2026 at 5:49 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Monday, February 16, 2026, Igor Korot <ikorot01@gmail.com> 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.

On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> 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.

Hi, David,

On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> 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.

And why there is no WHERE populated?

Thank you.

On Sat, Feb 28, 2026 at 6:05 PM Igor Korot <ikorot01@gmail.com> 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.

On Sat, Feb 28, 2026 at 6:34 PM Igor Korot <ikorot01@gmail.com> wrote:

And why there is no WHERE populated?

Thank you.

On Sat, Feb 28, 2026 at 7:05 PM Igor Korot <ikorot01@gmail.com> wrote:

Hi, David,

On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> 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.

Hi, David,

On Sat, Feb 28, 2026 at 7:42 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Sat, Feb 28, 2026 at 6:34 PM Igor Korot <ikorot01@gmail.com> wrote:

And why there is no WHERE populated?

Thank you.

On Sat, Feb 28, 2026 at 7:05 PM Igor Korot <ikorot01@gmail.com> wrote:

Hi, David,

On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> 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.

On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> 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.

Hi, David,

On Sat, Feb 28, 2026 at 11:49 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Saturday, February 28, 2026, Igor Korot <ikorot01@gmail.com> 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.

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