public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Igor Korot <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Where the info is stored
Date: Fri, 27 Feb 2026 09:17:38 -0700
Message-ID: <CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com>
References: <CA+FnnTwmt7BOqwZnsB5LOq47U+wzRc86wXaea9B0KEXfK2O+Kw@mail.gmail.com>
	<CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com>

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.

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Where the info is stored
  In-Reply-To: <CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox