MIME-Version: 1.0
References: <CA+FnnTwmt7BOqwZnsB5LOq47U+wzRc86wXaea9B0KEXfK2O+Kw@mail.gmail.com>
 <CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com> <CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com>
In-Reply-To: <CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com>
From: Igor Korot <ikorot01@gmail.com>
Date: Sat, 28 Feb 2026 18:37:34 -0600
Message-ID: <CA+FnnTz51Ond5HGkDgKvRB6ERi3pr5Q1wTnwoMK8rCwsm79_sA@mail.gmail.com>
Subject: Re: Where the info is stored
To: "David G. Johnston" <david.g.johnston@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTz51Ond5HGkDgKvRB6ERi3pr5Q1wTnwoMK8rCwsm79_sA%40mail.gmail.com>
Precedence: bulk

Hi, David,

On Fri, Feb 27, 2026 at 10:18=E2=80=AFAM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> Please keep replies on-list.
>
> On Mon, Feb 16, 2026 at 5:49=E2=80=AFPM David G. Johnston <david.g.johnst=
on@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 conten=
ts.
>
>
> 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 i=
ndkey
>     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 A=
S 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;

Just tried the following:

draft=3D# 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 =3D 50 ));
CREATE TABLE
draft=3D#
draft=3D#
draft=3D# 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 =3D
i.indexrelid AND ns.oid =3D ic.relnamespace AND ns.nspname =3D 'public'
AND ic.relname =3D '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 =3D
ords.indrelid AND a.attnum =3D 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 =3D 'p' AND n.nspname =3D 'public' AND cl.relname =3D
'leagues_new' AND cl.oid =3D co.conrelid AND n.oid =3D cl.relnamespace;
       name       | include | tablespace | with
------------------+---------+------------+------
 leagues_new_pkey |         | public     |
(1 row)

draft=3D#

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.

>
> David J.
>