Subject: Re: Where the info is stored

From: "David G. Johnston" <[email-masked]>
Date: Mon, 16 Feb 2026 17:49:19 -0700

On Monday, February 16, 2026, Igor Korot <[email-masked]> 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.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 17 Feb 2026 09:59:04 -0500

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

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 27 Feb 2026 09:17:38 -0700

Please keep replies on-list.

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

---

From: Igor Korot <[email-masked]>
Date: Sat, 28 Feb 2026 18:37:34 -0600

Hi, David,

On Fri, Feb 27, 2026 at 10:18 AM David G. Johnston
<[email-masked]> wrote:
> On Mon, Feb 16, 2026 at 5:49 PM David G. Johnston <[email-masked]> wrote:
>>
>     SELECT i.indexrelid, i.indrelid, i.indnkeyatts, i.indkey::int2[] AS indkey
>     FROM pg_index i
>     CASE WHEN ords.ord < ords.indnkeyatts THEN 'key' ELSE 'include' END AS column_role,
> --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.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 28 Feb 2026 18:02:31 -0700

On Saturday, February 28, 2026, Igor Korot <[email-masked]> wrote:

> FROM pg_constraint co, pg_namespace n, pg_class
>

Constraints don’t have included columns.  Only indexes do.  You need to
query the index, not the constraint.

David J.

---

From: Igor Korot <[email-masked]>
Date: Sat, 28 Feb 2026 19:05:39 -0600

Hi, David,

On Sat, Feb 28, 2026 at 7:02 PM David G. Johnston
<[email-masked]> wrote:
> 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.

---

From: Igor Korot <[email-masked]>
Date: Sat, 28 Feb 2026 19:34:47 -0600

And why there is no WHERE populated?

Thank you.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 28 Feb 2026 18:35:14 -0700

> I literally copied your query into my code and it didn't populated

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.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 28 Feb 2026 18:42:18 -0700

> > I literally copied your query into my code and it didn't populated
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.

---

From: Igor Korot <[email-masked]>
Date: Sat, 28 Feb 2026 23:34:58 -0600

Hi, David,

On Sat, Feb 28, 2026 at 7:42 PM David G. Johnston
<[email-masked]> wrote:
> 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.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 28 Feb 2026 22:49:13 -0700

On Saturday, February 28, 2026, Igor Korot <[email-masked]> wrote:

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

---

From: Igor Korot <[email-masked]>
Date: Sun, 1 Mar 2026 01:50:52 -0600

Hi, David,

On Sat, Feb 28, 2026 at 11:49 PM David G. Johnston
<[email-masked]> wrote:
> 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.

---

From: Igor Korot <[email-masked]>
Date: Sun, 1 Mar 2026 01:58:37 -0600

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.

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 01 Mar 2026 11:33:00 +0100

> 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