MIME-Version: 1.0
References: <CA+FnnTwmt7BOqwZnsB5LOq47U+wzRc86wXaea9B0KEXfK2O+Kw@mail.gmail.com>
 <CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com>
In-Reply-To: <CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 27 Feb 2026 09:17:38 -0700
Message-ID: <CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com>
Subject: Re: Where the info is stored
To: Igor Korot <ikorot01@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008583fe064bd09567"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa%2BQ%40mail.gmail.com>
Precedence: bulk

--0000000000008583fe064bd09567
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Please keep replies on-list.

On Mon, Feb 16, 2026 at 5:49=E2=80=AFPM 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 content=
s.
>

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 =3D i.indexrelid
    JOIN pg_namespace ns ON ns.oid =3D ic.relnamespace
    WHERE ns.nspname =3D 'public'
            AND ic.relname =3D '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 =3D ords.indexrelid
JOIN pg_namespace ns ON ns.oid =3D ic.relnamespace
JOIN pg_class tc ON tc.oid =3D ords.indrelid
JOIN pg_attribute a ON a.attrelid =3D ords.indrelid
                   AND a.attnum =3D 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.

--0000000000008583fe064bd09567
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">Please keep replies on-list.</span></div><div class=3D"gma=
il_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div=
 class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><=
span style=3D"font-family:Arial,Helvetica,sans-serif">On Mon, Feb 16, 2026 =
at 5:49=E2=80=AFPM David G. Johnston &lt;<a href=3D"mailto:david.g.johnston=
@gmail.com">david.g.johnston@gmail.com</a>&gt; wrote:</span></div></div><di=
v class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">On Monday, February 16, 2026, Igor Korot &lt;<a href=
=3D"mailto:ikorot01@gmail.com" target=3D"_blank">ikorot01@gmail.com</a>&gt;=
 wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
Where are included columns=C2=A0</blockquote><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><br></blockquote><div>=C2=A0</div><div>pg_attribute, th=
ough you need info from pg_index to interpret the contents.</div></blockquo=
te><div><br></div><div class=3D"gmail_default" style=3D"font-family:arial,h=
elvetica,sans-serif">Specifically:</div><div class=3D"gmail_default" style=
=3D"font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_d=
efault" style=3D"font-family:arial,helvetica,sans-serif">\set ON_ERROR_STOP=
 on<br><br>BEGIN;<br><br>CREATE TABLE wip_idx_include_demo (<br>=C2=A0 =C2=
=A0 id integer NOT NULL,<br>=C2=A0 =C2=A0 secondary_id integer NOT NULL,<br=
>=C2=A0 =C2=A0 included_payload text,<br>=C2=A0 =C2=A0 notes text,<br>=C2=
=A0 =C2=A0 CONSTRAINT wip_idx_include_demo_id_secondary_uq<br>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 UNIQUE (id, secondary_id) INCLUDE (included_payload)<br>);<b=
r><br>WITH idx AS (<br>=C2=A0 =C2=A0 SELECT i.indexrelid, i.indrelid, i.ind=
nkeyatts, i.indkey::int2[] AS indkey<br>=C2=A0 =C2=A0 FROM pg_index i<br>=
=C2=A0 =C2=A0 JOIN pg_class ic ON ic.oid =3D i.indexrelid<br>=C2=A0 =C2=A0 =
JOIN pg_namespace ns ON ns.oid =3D ic.relnamespace<br>=C2=A0 =C2=A0 WHERE n=
s.nspname =3D &#39;public&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 AND ic.relname =3D &#39;wip_idx_include_demo_id_secondary_uq&#39;<br>), or=
ds AS (<br>=C2=A0 =C2=A0 SELECT idx.indexrelid,<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0idx.indrelid,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
idx.indnkeyatts,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0s.ord,<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0idx.indkey[s.ord] AS attnum<br>=C2=A0=
 =C2=A0 FROM idx<br>=C2=A0 =C2=A0 CROSS JOIN LATERAL generate_subscripts(id=
x.indkey, 1) AS s(ord)<br>)<br>SELECT ns.nspname AS schema_name,<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0ic.relname AS index_name,<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0tc.relname AS table_name,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0a.attname AS col=
umn_name,<br>=C2=A0 =C2=A0 CASE WHEN ords.ord &lt; ords.indnkeyatts THEN &#=
39;key&#39; ELSE &#39;include&#39; END AS column_role,<br>=C2=A0 =C2=A0 ord=
s.ord + 1 AS index_position<br>FROM ords<br>JOIN pg_class ic ON ic.oid =3D =
ords.indexrelid<br>JOIN pg_namespace ns ON ns.oid =3D ic.relnamespace<br>JO=
IN pg_class tc ON tc.oid =3D ords.indrelid<br>JOIN pg_attribute a ON a.attr=
elid =3D ords.indrelid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0AND a.attnum =3D ords.attnum<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND NOT a.attisdropped<br>O=
RDER BY ords.ord \gx<br><br>\d+ wip_idx_include_demo_id_secondary_uq</div><=
div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif=
">--given that the above provides the relevant info Greg&#39;s suggestion w=
ould also get you a functioning base query.</div><div class=3D"gmail_defaul=
t" style=3D"font-family:arial,helvetica,sans-serif"><br>ROLLBACK;<br></div>=
<div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-seri=
f"><br></div><div class=3D"gmail_default" style=3D"font-family:arial,helvet=
ica,sans-serif">David J.</div><div class=3D"gmail_default" style=3D"font-fa=
mily:arial,helvetica,sans-serif"><br></div></div></div>

--0000000000008583fe064bd09567--