public inbox for [email protected]
help / color / mirror / Atom feedDoes included columns part of the PK
8+ messages / 3 participants
[nested] [flat]
* Does included columns part of the PK
@ 2026-03-14 07:09 Igor Korot <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Igor Korot @ 2026-03-14 07:09 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
Hi, ALL,
Have one weird question ;-)
When I connect with ODBC and call SQLPrimaryKey() the function returns
all fields including
"included" fields.
However running libpq and trying to get the table info with:
[quote]
L"SELECT DISTINCT column_name, data_type,
character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, is_nullable, column_default,
CASE WHEN column_name IN (SELECT ccu.column_name FROM
information_schema.constraint_column_usage ccu,
information_schema.table_constraints tc WHERE ccu.constraint_name =
tc.constraint_name AND tc.constraint_type = 'PRIMARY KEY' AND
ccu.table_name = $2) THEN 'YES' ELSE 'NO' END AS is_pk,
ordinal_position FROM information_schema.columns col,
information_schema.table_constraints tc WHERE tc.table_schema =
col.table_schema AND tc.table_name = col.table_name AND
col.table_schema = $1 AND col.table_name = $2 ORDER BY
ordinal_position;";
[/quote]
I'm getting only direct PK fields.
I presume that libpq is correct and the ODBC driver is the one that
needs to be fixed.
Just wanted to ask this list before going to complain to the ODBC list... ;-)
Thank you.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-14 07:12 Igor Korot <[email protected]>
parent: Igor Korot <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Igor Korot @ 2026-03-14 07:12 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
Below is the version info I use on this machine:
Calculating dependencies... done!
Dependency resolution took 53.38 s (backtrack: 0/20).
[ebuild R ] dev-db/postgresql-16.2:16::gentoo USE="icu lz4 nls
pam readline ssl xml zlib zstd -debug -doc -kerberos -ldap -llvm -perl
-python (-selinux) -server* -static-libs -systemd -tcl -uuid"
PYTHON_SINGLE_TARGET="python3_11 -python3_10 -python3_12" 0 KiB
[ebuild R ] dev-db/psqlodbc-11.01.0000::gentoo USE="ssl -doc
-iodbc -threads" 0 KiB
Total: 2 packages (2 reinstalls), Size of downloads: 0 KiB
WaylandGnome /home/igor/dbhandler/Debug #
Thank you.
On Sat, Mar 14, 2026 at 2:09 AM Igor Korot <[email protected]> wrote:
>
> Hi, ALL,
> Have one weird question ;-)
>
> When I connect with ODBC and call SQLPrimaryKey() the function returns
> all fields including
> "included" fields.
>
> However running libpq and trying to get the table info with:
>
> [quote]
> L"SELECT DISTINCT column_name, data_type,
> character_maximum_length, character_octet_length, numeric_precision,
> numeric_precision_radix, numeric_scale, is_nullable, column_default,
> CASE WHEN column_name IN (SELECT ccu.column_name FROM
> information_schema.constraint_column_usage ccu,
> information_schema.table_constraints tc WHERE ccu.constraint_name =
> tc.constraint_name AND tc.constraint_type = 'PRIMARY KEY' AND
> ccu.table_name = $2) THEN 'YES' ELSE 'NO' END AS is_pk,
> ordinal_position FROM information_schema.columns col,
> information_schema.table_constraints tc WHERE tc.table_schema =
> col.table_schema AND tc.table_name = col.table_name AND
> col.table_schema = $1 AND col.table_name = $2 ORDER BY
> ordinal_position;";
> [/quote]
>
> I'm getting only direct PK fields.
>
> I presume that libpq is correct and the ODBC driver is the one that
> needs to be fixed.
>
> Just wanted to ask this list before going to complain to the ODBC list... ;-)
>
> Thank you.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-14 15:07 David G. Johnston <[email protected]>
parent: Igor Korot <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2026-03-14 15:07 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Saturday, March 14, 2026, Igor Korot <[email protected]> wrote:
>
> I presume that libpq is correct and the ODBC driver is the one that
> needs to be fixed.
>
Odbc does seem to be in the wrong here, yes.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-14 15:25 Adrian Klaver <[email protected]>
parent: Igor Korot <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2026-03-14 15:25 UTC (permalink / raw)
To: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On 3/14/26 12:09 AM, Igor Korot wrote:
> Hi, ALL,
> Have one weird question ;-)
>
> When I connect with ODBC and call SQLPrimaryKey() the function returns
Are you actually using the SQLPrimaryKeys(), not SQLPrimaryKey()?
> all fields including
> "included" fields.
Define 'included' fields.
> I'm getting only direct PK fields.
Define direct PK fields.
>
> I presume that libpq is correct and the ODBC driver is the one that
> needs to be fixed.
Returning the actual results from each case would help.
Have you cranked up the logging on the Postgres server to see what query
ODBC is using?
>
> Just wanted to ask this list before going to complain to the ODBC list... ;-)
>
> Thank you.
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-14 15:51 David G. Johnston <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2026-03-14 15:51 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Saturday, March 14, 2026, Adrian Klaver <[email protected]>
wrote:
> On 3/14/26 12:09 AM, Igor Korot wrote:
>
>> Hi, ALL,
>> Have one weird question ;-)
>>
>> When I connect with ODBC and call SQLPrimaryKey() the function returns
>>
>
> Are you actually using the SQLPrimaryKeys(), not SQLPrimaryKey()?
>
> all fields including
>> "included" fields.
>>
>
> Define 'included' fields.
>
They are referring to the unique index that backs the PK constraint.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-14 16:56 Igor Korot <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Igor Korot @ 2026-03-14 16:56 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi,
On Sat, Mar 14, 2026 at 8:51 AM David G. Johnston <
[email protected]> wrote:
> On Saturday, March 14, 2026, Adrian Klaver <[email protected]>
> wrote:
>
>> On 3/14/26 12:09 AM, Igor Korot wrote:
>>
>>> Hi, ALL,
>>> Have one weird question ;-)
>>>
>>> When I connect with ODBC and call SQLPrimaryKey() the function returns
>>>
>>
>> Are you actually using the SQLPrimaryKeys(), not SQLPrimaryKey()?
>>
>> all fields including
>>> "included" fields.
>>>
>>
>> Define 'included' fields.
>>
>
> They are referring to the unique index that backs the PK constraint.
>
Not “they” - “He”. 😊
And yes - that’s what I’m referring to.
Thank you.
> David J.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-14 23:30 Adrian Klaver <[email protected]>
parent: Igor Korot <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2026-03-14 23:30 UTC (permalink / raw)
To: Igor Korot <[email protected]>; David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On 3/14/26 9:56 AM, Igor Korot wrote:
> Hi,
>
> On Sat, Mar 14, 2026 at 8:51 AM David G. Johnston
> <[email protected] <mailto:[email protected]>> wrote:
>
> On Saturday, March 14, 2026, Adrian Klaver
> <[email protected] <mailto:[email protected]>> wrote:
>
> On 3/14/26 12:09 AM, Igor Korot wrote:
>
> Hi, ALL,
> Have one weird question ;-)
>
> When I connect with ODBC and call SQLPrimaryKey() the
> function returns
>
>
> Are you actually using the SQLPrimaryKeys(), not SQLPrimaryKey()?
>
> all fields including
> "included" fields.
>
>
> Define 'included' fields.
>
>
> They are referring to the unique index that backs the PK constraint.
>
>
> Not “they” - “He”. 😊
>
> And yes - that’s what I’m referring to.
From here:
https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=functions-sqlprimarykeys-get-primary-key-column...:
1 TABLE_CAT VARCHAR(128) This is always null.
2 TABLE_SCHEM VARCHAR(128) The name of the schema containing TABLE_NAME.
3 TABLE_NAME VARCHAR(128) NOT NULL Name of the specified table.
4 COLUMN_NAME VARCHAR(128) NOT NULL Primary key column name.
5 KEY_SEQ SMALLINT NOT NULL Column sequence number in the primary key,
starting with 1.
6 PK_NAME VARCHAR(128) Primary key identifier. Contains a null value if
not applicable to the data
From here:
https://github.com/postgresql-interfaces/psqlodbc/blob/main/info.c
At line 4035 in SQLPrimaryKeys()
For case 1
/*
* Simplified query to remove assumptions about number of
* possible index columns. Courtesy of Tom Lane - thomas
* 2000-03-21
*/
[...]
"select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname"
" from pg_catalog.pg_attribute ta ... "
case 2
select ta.attname, ia.attnum, ic.relname, n.nspname, NULL"
" from pg_catalog.pg_attribute ta, ..."
If I am following correctly then:
attname = column_name
attnum = key_seq
ic.relname = pk_name
nspname = table_schem
tc.relname = table_name
So how are using it in your code and what are the actual results?
Also what is showing up in the Postgres logs?
>
> Thank you.
>
>
> David J.
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does included columns part of the PK
@ 2026-03-15 06:24 Igor Korot <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Igor Korot @ 2026-03-15 06:24 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, Adrian,
On Sat, Mar 14, 2026 at 6:30 PM Adrian Klaver <[email protected]> wrote:
>
> On 3/14/26 9:56 AM, Igor Korot wrote:
> > Hi,
> >
> > On Sat, Mar 14, 2026 at 8:51 AM David G. Johnston
> > <[email protected] <mailto:[email protected]>> wrote:
> >
> > On Saturday, March 14, 2026, Adrian Klaver
> > <[email protected] <mailto:[email protected]>> wrote:
> >
> > On 3/14/26 12:09 AM, Igor Korot wrote:
> >
> > Hi, ALL,
> > Have one weird question ;-)
> >
> > When I connect with ODBC and call SQLPrimaryKey() the
> > function returns
> >
> >
> > Are you actually using the SQLPrimaryKeys(), not SQLPrimaryKey()?
> >
> > all fields including
> > "included" fields.
> >
> >
> > Define 'included' fields.
> >
> >
> > They are referring to the unique index that backs the PK constraint.
> >
> >
> > Not “they” - “He”. 😊
> >
> > And yes - that’s what I’m referring to.
>
> From here:
>
>
> https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=functions-sqlprimarykeys-get-primary-key-column...:
>
> 1 TABLE_CAT VARCHAR(128) This is always null.
> 2 TABLE_SCHEM VARCHAR(128) The name of the schema containing TABLE_NAME.
> 3 TABLE_NAME VARCHAR(128) NOT NULL Name of the specified table.
> 4 COLUMN_NAME VARCHAR(128) NOT NULL Primary key column name.
> 5 KEY_SEQ SMALLINT NOT NULL Column sequence number in the primary key,
> starting with 1.
> 6 PK_NAME VARCHAR(128) Primary key identifier. Contains a null value if
> not applicable to the data
>
>
Lets start with the beginning:
draft=# 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 ));
CREATE TABLE
draft=#
> From here:
>
> https://github.com/postgresql-interfaces/psqlodbc/blob/main/info.c
>
> At line 4035 in SQLPrimaryKeys()
>
> For case 1
>
> /*
> * Simplified query to remove assumptions about number of
> * possible index columns. Courtesy of Tom Lane - thomas
> * 2000-03-21
> */
>
> [...]
>
> "select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname"
> " from pg_catalog.pg_attribute ta ... "
>
>
> case 2
>
> select ta.attname, ia.attnum, ic.relname, n.nspname, NULL"
> " from pg_catalog.pg_attribute ta, ..."
>
> If I am following correctly then:
>
> attname = column_name
> attnum = key_seq
> ic.relname = pk_name
> nspname = table_schem
> tc.relname = table_name
>
>
> So how are using it in your code and what are the actual results?
>
> Also what is showing up in the Postgres logs?
>
> >
> > Thank you.
> >
> >
> > David J.
> >
>
>
> --
> Adrian Klaver
> [email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-03-15 06:24 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-14 07:09 Does included columns part of the PK Igor Korot <[email protected]>
2026-03-14 07:12 ` Igor Korot <[email protected]>
2026-03-14 15:07 ` David G. Johnston <[email protected]>
2026-03-14 15:25 ` Adrian Klaver <[email protected]>
2026-03-14 15:51 ` David G. Johnston <[email protected]>
2026-03-14 16:56 ` Igor Korot <[email protected]>
2026-03-14 23:30 ` Adrian Klaver <[email protected]>
2026-03-15 06:24 ` Igor Korot <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox