Re: Does included columns part of the PK - Mailing list pgsql-general

On 3/16/26 2:51 PM, Igor Korot wrote:
> Adrian,
> 
> On Mon, Mar 16, 2026 at 2:40 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 3/16/26 2:30 PM, Adrian Klaver wrote:
>>> On 3/16/26 11:47 AM, Igor Korot wrote:
>>>
>>> Reply to list also.
>>> Ccing list.
>>>> Adrian,
>>>>
>>>>
>>>>
>>>> On Mon, Mar 16, 2026, 8:03 AM Adrian Klaver <adrian.klaver@aklaver.com
>>>> <mailto:adrian.klaver@aklaver.com>> wrote:
>>>>
>>>>      On 3/15/26 6:23 PM, Igor Korot wrote:
>>>>       > Hi, Adrian,
>>>>       >
>>>>       > Here is the log file from running in ODBC mode: https://bpa.st/
>>>>      Z2DWG <https://bpa.st/Z2DWG>
>>>>
>>>>      I have no idea what this is trying to show?
>>>>
>>>>
>>>> The log shows ODBC connection and then at the end - call to
>>>> SQLPrimaryKeys().
>>>
>>> Where?
>>>
>>> I don't see that in either the link you posted or the text file Greg
>>> sent to the list.
>>>
>>>>
>>>> If you run it against the table I posted above, you will get 3 fields.
>>>> Whereas it should be just one.
>>>
>>> Again, run it how?
>>
>> Alright I see what you are talking about now. I'm not using the ODBC
>> driver just it's query. In psql :
>>
>> 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, deduplicate_items = OFF)
>> );
>>
>>
>> SELECT
>>       ta.attname,
>>       ia.attnum,
>>       ic.relname,
>>       n.nspname,
>>       tc.relname
>> FROM
>>       pg_catalog.pg_attribute ta,
>>       pg_catalog.pg_attribute ia,
>>       pg_catalog.pg_class tc,
>>       pg_catalog.pg_index i,
>>       pg_catalog.pg_namespace n,
>>       pg_catalog.pg_class ic
>> WHERE
>>       tc.relname = 'leagues_new'
>>       AND n.nspname = 'public'
>>       AND tc.oid = i.indrelid
>>       AND n.oid = tc.relnamespace
>>       AND i.indisprimary = 't'
>>       AND ia.attrelid = i.indexrelid
>>       AND ta.attrelid = i.indrelid
>>       AND ta.attnum = i.indkey[ia.attnum - 1]
>>       AND (NOT ta.attisdropped)
>>       AND (NOT ia.attisdropped)
>>       AND ic.oid = i.indexrelid
>> ORDER BY
>>       ia.attnum;
>>
>> yields
>>
>> attname   | attnum |     relname      | nspname |   relname
>> -------------+--------+------------------+---------+-------------
>>    id          |      1 | leagues_new_pkey | public  | leagues_new
>>    drafttype   |      2 | leagues_new_pkey | public  | leagues_new
>>    scoringtype |      3 | leagues_new_pkey | public  | leagues_new
> 
> Correct.
> 
> And according to the second reply it should yeld just the first record.
> 
> I'm going to forward this to the ODBC list...

I have not worked it out yet but would start with:

AND ta.attnum = i.indkey[ia.attnum - 1]

per

www.postgresql.org/docs/current/catalog-pg-index.html

"
indkey int2vector (references pg_attribute.attnum)

...

This is an array of indnatts values that indicate which table columns 
this index indexes. For example, a value of 1 3 would mean that the 
first and the third table columns make up the index entries. Key columns 
come before non-key (included) columns.

...
"

Though there is the below from the same page:

"indnatts int2

The total number of columns in the index (duplicates pg_class.relnatts); 
this number includes both key and included attributes

indnkeyatts int2

The number of key columns in the index, not counting any included 
columns, which are merely stored and do not participate in the index 
semantics
"


> 
> Thank you.
> 
>>
>>
>>>
>>>>
>>>> Thank you.
>>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com