On Sat, Feb 28, 2026 at 6:05 PM Igor Korot <[email protected]> wrote:
> I literally copied your query into my code and it didn't populated
> anything...
>
Without showing your work that tells me nothing.
> Am I missing something?
>
>
Apparently. I don't have the desire to play 20 questions over email to
figure out what though. Here's the fish.
\set ON_ERROR_STOP on
BEGIN;
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 = 50)
);
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 = format('%s_pkey', '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
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+ leagues_new_pkey
ROLLBACK;
psql --file wip/index-include-scratch.psql
BEGIN
CREATE TABLE
-[ RECORD 1 ]--+-----------------
schema_name | public
index_name | leagues_new_pkey
table_name | leagues_new
column_name | id
column_role | key
index_position | 1
-[ RECORD 2 ]--+-----------------
schema_name | public
index_name | leagues_new_pkey
table_name | leagues_new
column_name | drafttype
column_role | include
index_position | 2
-[ RECORD 3 ]--+-----------------
schema_name | public
index_name | leagues_new_pkey
table_name | leagues_new
column_name | scoringtype
column_role | include
index_position | 3
Index "public.leagues_new_pkey"
Column | Type | Key? | Definition | Storage | Stats target
-------------+----------+------+-------------+---------+--------------
id | integer | yes | id | plain |
drafttype | smallint | no | drafttype | plain |
scoringtype | smallint | no | scoringtype | plain |
primary key, btree, for table "public.leagues_new"
Options: fillfactor=50
ROLLBACK
That is purely AI generated but does produce the correct value and looks
quite reasonable. There may very well be a more idiomatic way to do this,
but I don't write these kinds of queries myself. As previously stated,
feel free to see what psql is sending to the server to produce the second,
\d+, result if you want another query form to consider.
Note, though, the absence of pg_constraint anywhere in this query.
David J.
