Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vwViu-00BV9M-0y for pgsql-general@arkaria.postgresql.org; Sun, 01 Mar 2026 01:35:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vwVit-00C2sP-0a for pgsql-general@arkaria.postgresql.org; Sun, 01 Mar 2026 01:35:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vwVis-00C2sG-2i for pgsql-general@lists.postgresql.org; Sun, 01 Mar 2026 01:35:54 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vwVip-00000001o1e-3t81 for pgsql-general@lists.postgresql.org; Sun, 01 Mar 2026 01:35:54 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-66ee7b9af94so1451708eaf.0 for ; Sat, 28 Feb 2026 17:35:52 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772328950; cv=none; d=google.com; s=arc-20240605; b=KPWxE0TU6rP6bEXr8MzzzjkWe1Ou1eKLWXjmUKD3m2MwgEblQrvM/hvgQp74uNqeMo EwGFwvbxWVajBXV6XQTGQJhK4vLmzMUv+pdbzcnLtjM883bFQ7X5xJKG8Nu7V+Yq1ype qWRWXh3zN2Zqax4D0Q3nNSs7YAmdJa965GhnM13iQFmmGorBYdI2a2C6mt/eHAMJ7VMJ NYxnNkT+DXFwWPh1VkpHpEhnHqP8CwPpKOX76GMBr5fS37e5T+zflwRFcNIrUXaKchKt 2glIn7OBvdatHHJH8doDwVCN/zR/IdvMUyxFL5R+m3HOikDShx6dqut+REVfF3/p6OJp wdzw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=BIMAzzLBUxu/afyOW3W4vCpI1LEFTvoYPbMCewwKO98=; fh=MqX3/PjeIRWyveucHIwmT3MEzIvphFWgoM5FEnVDTkI=; b=Fm1LZ9XzPk6ntdBxzQlZZaEaXQGYVKph9m9G8nwk4R9hHOUeKAZ1ooEUNZfxBj9YkR tAdcGKyQkGYlrBSz1huO1ggDLw5eZZBeQ3ODU30vBTmJdukakqhUTBN1dWPjYOiX9Wc/ 8tjMDezbt3CS18Jc4wIq6u48c3kttE3nQc/rY8vOxB9rMzr+ZvYbsyFFGJa2WlOlbR0Y l+R4jyMvNmBCAEUID6Mn83hgKLdX+EC0RD360zGnP7aCpbyFbvbtrPFHezyvk8IljhQy uJqpeUmoopiXTV439TD3M+dXx2Tw1aONacR4MuE1GSg3L4N5JxfaKILjIMI+137Q4J3R EozA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772328950; x=1772933750; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=BIMAzzLBUxu/afyOW3W4vCpI1LEFTvoYPbMCewwKO98=; b=RzNr8hyS7BhCwlRh/c4obAx80a8u/yooD76/nC/PIXmd1fRzHPcpkrajGQnt8lw0mK nUFLWz+Yurxx3SQMyjEf874hFx84e+Nr8/Jz8XwuPrMAQ29y9V4eGtGXLwvMczslGkZK H4JCsopWly6oZMwADEmNl4AVp/hhvbgdYDG3xTngNKHVBh4g147pXdjMC7gV4qZUkbqa y3UPPH+fTL+la85M+n/K7ZKmIPqC8kqm0beeLpNGrAhJJ7t992kqD2wCRmMuxorEE7pc ncqlKxv+fhUgfdshVpCCZkhQ627oAfH5oZHJeMkenKdrWt2kdGkXY/J3Zyk6TJYOSM2s NkBg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772328950; x=1772933750; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=BIMAzzLBUxu/afyOW3W4vCpI1LEFTvoYPbMCewwKO98=; b=MY2g9SL9kmtMw9OQSQit2WHJ3irmsqUabQuOTcmCZ5xJfsHuj/YOBDlFLoTwsdDba5 dt/o9xEnFBBz2GKALTBqnB4v8aE1PtKnrtBrQLAiWy7RcjU/CxqqDyKLHkfyg5IgcujH cn2igoMTTPXKmCseEJiB+aHT+VnxPlqlj5NyhLh5CPcjhQ2JoC/VCJQ4oW0DAq+nxhsX Hg30izQ5/qPEXnu9LL2tKc0whR106JCzHIOHelx0ATDXDhgxCQyTGqZi1Fy127uWe68E DoNIDXZhUpnkOthytnAFYXcA8Xi48o5vd1j5xP6o5wibwUm7kTLcl0JU1zBFVw9WkZ6u Nrtw== X-Gm-Message-State: AOJu0Yz7PcIfPINBZe6Tm/FXCdsyq2NUjmKVlPu74gXSR/OVUN/lw3jh sVy6mXzu04RMQ0f5H0IQcvog9EnOG2CmKhO12U2fIAPxwt5tFB8D0aAEFoGSaTMwFsEHYgipfJ5 osToZI3MZKsp8iX7CkGo2xKiow7Gd+kQ= X-Gm-Gg: ATEYQzzqorR/JTSSgt7ED6eoI2SvEdPhwMQoz5+hQLdEnj6sg2YT2zUNbm+shhyxm6r GfoKxymxHMUbxZBqfWZNOCUSiuorCy113or3y5E3rliC47eWBIJXKohDDAICmRojz6SlylDNsJq /Fqkak0yKJauis24x9fey5rzGLabn0YqW9M/1FBcqTasPGX2bZJNPK231FjYUPUSpTOCIm0tmOT ZjELR4YBSndzOexc3M4wJOnZoaTC7RMLBzDT2P+WGqxF3Mbth3PFBobuqfeN957HwuXWO/qh7Bx 43f6ixA= X-Received: by 2002:a05:6820:2014:b0:678:cf78:1e90 with SMTP id 006d021491bc7-679fae7c5a4mr4564798eaf.31.1772328950515; Sat, 28 Feb 2026 17:35:50 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Sat, 28 Feb 2026 18:35:14 -0700 X-Gm-Features: AaiRm52W19qSjlIEBEaUJR7YrG74YGX5au_nXrVNAGDw6bwpVCy4tKexm6PKO7A Message-ID: Subject: Re: Where the info is stored To: Igor Korot Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000854ce9064bec7dd6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000854ce9064bec7dd6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Feb 28, 2026 at 6:05=E2=80=AFPM Igor Korot wro= te: > 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 =3D = 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 =3D i.indexrelid JOIN pg_namespace ns ON ns.oid =3D ic.relnamespace WHERE ns.nspname =3D 'public' AND ic.relname =3D 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 =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+ 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=3D50 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. --000000000000854ce9064bec7dd6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sat, Feb 28, 2026 at 6:05=E2=80=AFPM Igor Korot <ikorot01@gmail.com> wrote:=
I literally copied your query into my code and it didn't populated
anything...

Without showing your work tha= t tells me nothing.


Am I missing something?

=C2=A0
Appar= ently.=C2=A0 I don't have the desire to play 20 questions over email to= figure out what though.=C2=A0 Here's the fish.

\set ON_ERROR_STOP on

BEGIN;

CREATE TABLE leagues_ne= w (
=C2=A0 =C2=A0 id serial,
=C2=A0 =C2=A0 name varchar(100),
=C2= =A0 =C2=A0 drafttype smallint,
=C2=A0 =C2=A0 scoringtype smallint,
= =C2=A0 =C2=A0 roundvalues smallint,
=C2=A0 =C2=A0 leaguetype char(5),=C2=A0 =C2=A0 salary integer,
=C2=A0 =C2=A0 benchplayers smallint,
= =C2=A0 =C2=A0 PRIMARY KEY (id) INCLUDE (drafttype, scoringtype) WITH (fillf= actor =3D 50)
);

WITH idx AS (
=C2=A0 =C2=A0 SELECT
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 i.indexrelid,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 i.indreli= d,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 i.indnkeyatts,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 i.indkey::int2[] AS indkey
=C2=A0 =C2=A0 FROM pg_index i
=C2= =A0 =C2=A0 JOIN pg_class ic ON ic.oid =3D i.indexrelid
=C2=A0 =C2=A0 JOI= N pg_namespace ns ON ns.oid =3D ic.relnamespace
=C2=A0 =C2=A0 WHERE ns.n= spname =3D 'public'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AN= D ic.relname =3D format('%s_pkey', 'leagues_new')
), ord= s AS (
=C2=A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2=A0 =C2=A0 idx.indexreli= d,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 idx.indrelid,
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 idx.indnkeyatts,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 s.ord,
=C2=A0 =C2=A0= =C2=A0 =C2=A0 idx.indkey[s.ord] AS attnum
=C2=A0 =C2=A0 FROM idx
=C2= =A0 =C2=A0 CROSS JOIN LATERAL generate_subscripts(idx.indkey, 1) AS s(ord)<= br>)
SELECT
=C2=A0 =C2=A0 ns.nspname AS schema_name,
=C2=A0 =C2=A0= ic.relname AS index_name,
=C2=A0 =C2=A0 tc.relname AS table_name,
= =C2=A0 =C2=A0 a.attname AS column_name,
=C2=A0 =C2=A0 CASE
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 WHEN ords.ord < ords.indnkeyatts THEN 'key'=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE 'include'
=C2=A0 =C2=A0 END AS= column_role,
=C2=A0 =C2=A0 ords.ord + 1 AS index_position
FROM ords<= br>JOIN pg_class ic ON ic.oid =3D ords.indexrelid
JOIN pg_namespace ns O= N ns.oid =3D ic.relnamespace
JOIN pg_class tc ON tc.oid =3D ords.indreli= d
JOIN pg_attribute a ON a.attrelid =3D ords.indrelid
=C2=A0 =C2=A0 A= ND a.attnum =3D ords.attnum
=C2=A0 =C2=A0 AND NOT a.attisdropped
ORDE= R BY ords.ord \gx

\d+ leagues_new_pkey

ROLLBACK;

psql --file wip/index-include-scratch.psql
BEGIN
CR= EATE TABLE
-[ RECORD 1 ]--+-----------------
schema_name =C2=A0 =C2= =A0| public
index_name =C2=A0 =C2=A0 | leagues_new_pkey
table_name = =C2=A0 =C2=A0 | leagues_new
column_name =C2=A0 =C2=A0| id
column_role= =C2=A0 =C2=A0| key
index_position | 1
-[ RECORD 2 ]--+--------------= ---
schema_name =C2=A0 =C2=A0| public
index_name =C2=A0 =C2=A0 | leag= ues_new_pkey
table_name =C2=A0 =C2=A0 | leagues_new
column_name =C2= =A0 =C2=A0| drafttype
column_role =C2=A0 =C2=A0| include
index_positi= on | 2
-[ RECORD 3 ]--+-----------------
schema_name =C2=A0 =C2=A0| p= ublic
index_name =C2=A0 =C2=A0 | leagues_new_pkey
table_name =C2=A0 = =C2=A0 | leagues_new
column_name =C2=A0 =C2=A0| scoringtype
column_ro= le =C2=A0 =C2=A0| include
index_position | 3

=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index "public.leagues= _new_pkey"
=C2=A0 =C2=A0Column =C2=A0 =C2=A0| =C2=A0 Type =C2=A0 | = Key? | Definition =C2=A0| Storage | Stats target
-------------+--------= --+------+-------------+---------+--------------
=C2=A0id =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| integer =C2=A0| yes =C2=A0| id =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| plain =C2=A0 |
=C2=A0drafttype =C2=A0 | smallint | no = =C2=A0 | drafttype =C2=A0 | plain =C2=A0 |
=C2=A0scoringtype | smallint= | no =C2=A0 | scoringtype | plain =C2=A0 |
primary key, btree, for tab= le "public.leagues_new"
Options: fillfactor=3D50

ROLLBA= CK

That is purely AI generated but does produce= the correct value and looks quite reasonable.=C2=A0 There may very well be= a more idiomatic way to do this, but I don't write these kinds of quer= ies myself.=C2=A0 As previously stated, feel free to see what psql is sendi= ng to the server to produce the second, \d+, result if you want another que= ry form to consider.

Note, though, the absence of pg= _constraint anywhere in this query.

David J.
<= br>
--000000000000854ce9064bec7dd6--