Re: Where the info is stored

Please keep replies on-list.

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

> On Monday, February 16, 2026, Igor Korot <ikorot01(at)gmail(dot)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.