public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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: Sat, 28 Feb 2026 18:35:14 -0700
Message-ID: <CAKFQuwaKw87g6rOYsKMQHn5nuibg+6q5ssT9Jehhcyk3_Nxfsg@mail.gmail.com> (raw)
In-Reply-To: <CA+FnnTw0bNsDBKFEQKsrjFZKqiZgRVZ27zhgXs5sqCLW=bwBVw@mail.gmail.com>
References: <CA+FnnTwmt7BOqwZnsB5LOq47U+wzRc86wXaea9B0KEXfK2O+Kw@mail.gmail.com>
<CAKFQuwa2ef9Bxz79Q8JtVNxmMABN829EJqiLKXxB5=5AEHyr9w@mail.gmail.com>
<CAKFQuwa9PsXitAWuG1hg-8EPOu0AdB2TiANaBRJfgSrdjioa+Q@mail.gmail.com>
<CA+FnnTz51Ond5HGkDgKvRB6ERi3pr5Q1wTnwoMK8rCwsm79_sA@mail.gmail.com>
<CAKFQuwa7rK-NfVwHT7_Nob6S=GN-tKK7F1+eLQ-FQZMsW3gd8g@mail.gmail.com>
<CA+FnnTw0bNsDBKFEQKsrjFZKqiZgRVZ27zhgXs5sqCLW=bwBVw@mail.gmail.com>
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.
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: <CAKFQuwaKw87g6rOYsKMQHn5nuibg+6q5ssT9Jehhcyk3_Nxfsg@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