Re: Where the info is stored - Mailing list pgsql-general

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.