public inbox for [email protected]help / color / mirror / Atom feed
Re: Does included columns part of the PK 7+ messages / 2 participants [nested] [flat]
* Re: Does included columns part of the PK @ 2026-03-16 21:51 Igor Korot <[email protected]> 2026-03-16 21:53 ` Fwd: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-16 21:57 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Igor Korot @ 2026-03-16 21:51 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general Adrian, On Mon, Mar 16, 2026 at 2:40 PM Adrian Klaver <[email protected]> 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 <[email protected] > >> <mailto:[email protected]>> 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... Thank you. > > > > > >> > >> Thank you. > >> > > > -- > Adrian Klaver > [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Fwd: Does included columns part of the PK 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> @ 2026-03-16 21:53 ` Igor Korot <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Igor Korot @ 2026-03-16 21:53 UTC (permalink / raw) To: PostgreSQL ODBC list <[email protected]> Hi, List. This is what I found out. Could someone please look into it? Thank you. ---------- Forwarded message --------- From: Igor Korot <[email protected]> Date: Mon, Mar 16, 2026 at 2:51 PM Subject: Re: Does included columns part of the PK To: Adrian Klaver <[email protected]> Cc: pgsql-general <[email protected]> Adrian, On Mon, Mar 16, 2026 at 2:40 PM Adrian Klaver <[email protected]> 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 <[email protected] > >> <mailto:[email protected]>> 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... Thank you. > > > > > >> > >> Thank you. > >> > > > -- > Adrian Klaver > [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does included columns part of the PK 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> @ 2026-03-16 21:57 ` Adrian Klaver <[email protected]> 2026-03-16 23:38 ` Re: Does included columns part of the PK Igor Korot <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2026-03-16 21:57 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: pgsql-general On 3/16/26 2:51 PM, Igor Korot wrote: > Adrian, > > On Mon, Mar 16, 2026 at 2:40 PM Adrian Klaver <[email protected]> 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 <[email protected] >>>> <mailto:[email protected]>> 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 >> [email protected] -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does included columns part of the PK 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-16 21:57 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> @ 2026-03-16 23:38 ` Igor Korot <[email protected]> 2026-03-17 00:44 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Igor Korot @ 2026-03-16 23:38 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general Adrian, Most important - do you agree that those fields should not be in this recordset? Thank you. On Mon, Mar 16, 2026 at 2:57 PM Adrian Klaver <[email protected]> wrote: > > On 3/16/26 2:51 PM, Igor Korot wrote: > > Adrian, > > > > On Mon, Mar 16, 2026 at 2:40 PM Adrian Klaver <[email protected]> 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 <[email protected] > >>>> <mailto:[email protected]>> 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 > >> [email protected] > > > -- > Adrian Klaver > [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does included columns part of the PK 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-16 21:57 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> 2026-03-16 23:38 ` Re: Does included columns part of the PK Igor Korot <[email protected]> @ 2026-03-17 00:44 ` Adrian Klaver <[email protected]> 2026-03-17 01:11 ` Re: Does included columns part of the PK Igor Korot <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2026-03-17 00:44 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: pgsql-general On 3/16/26 4:38 PM, Igor Korot wrote: > Adrian, > > Most important - do you agree that those fields should not be in this > recordset? I don't see anything here: https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlprimarykeys-function?view=sql-server-... that says they should be returned. Though the above also says: " Additional columns beyond column 6 (PK_NAME) can be defined by the driver." That is not the case here, where additional rows are used. However it does allow for more information to be supplied. My purely un-expert analysis is, no the extra rows should not be there. > > Thank you. -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does included columns part of the PK 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-16 21:57 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> 2026-03-16 23:38 ` Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-17 00:44 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> @ 2026-03-17 01:11 ` Igor Korot <[email protected]> 2026-04-21 05:33 ` Re: Does included columns part of the PK Igor Korot <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Igor Korot @ 2026-03-17 01:11 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general Adrian,, On Mon, Mar 16, 2026 at 5:44 PM Adrian Klaver <[email protected]> wrote: > > On 3/16/26 4:38 PM, Igor Korot wrote: > > Adrian, > > > > Most important - do you agree that those fields should not be in this > > recordset? > > I don't see anything here: > > https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlprimarykeys-function?view=sql-server-... > > that says they should be returned. Though the above also says: > > " Additional columns beyond column 6 (PK_NAME) can be defined by the > driver." > > That is not the case here, where additional rows are used. However it > does allow for more information to be supplied. > > My purely un-expert analysis is, no the extra rows should not be there. Thx for confirming. I already forwarded the email with the query results over to ODBC list. Hopefully someone can come back soon. Are you reading that list as well? If not I will update this thread with the results. Thank you. > > > > > Thank you. > > -- > Adrian Klaver > [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does included columns part of the PK 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-16 21:57 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> 2026-03-16 23:38 ` Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-17 00:44 ` Re: Does included columns part of the PK Adrian Klaver <[email protected]> 2026-03-17 01:11 ` Re: Does included columns part of the PK Igor Korot <[email protected]> @ 2026-04-21 05:33 ` Igor Korot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Igor Korot @ 2026-04-21 05:33 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general For all involved and interested: The issue with ODBC driver has been fixed and will be part of the next release. Ref: https://github.com/postgresql-interfaces/psqlodbc/issues/170. Thank you. On Mon, Mar 16, 2026 at 6:11 PM Igor Korot <[email protected]> wrote: > > Adrian,, > > On Mon, Mar 16, 2026 at 5:44 PM Adrian Klaver <[email protected]> wrote: > > > > On 3/16/26 4:38 PM, Igor Korot wrote: > > > Adrian, > > > > > > Most important - do you agree that those fields should not be in this > > > recordset? > > > > I don't see anything here: > > > > https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlprimarykeys-function?view=sql-server-... > > > > that says they should be returned. Though the above also says: > > > > " Additional columns beyond column 6 (PK_NAME) can be defined by the > > driver." > > > > That is not the case here, where additional rows are used. However it > > does allow for more information to be supplied. > > > > My purely un-expert analysis is, no the extra rows should not be there. > > Thx for confirming. > > I already forwarded the email with the query results over to ODBC list. > > Hopefully someone can come back soon. > > Are you reading that list as well? If not I will update this thread > with the results. > > Thank you. > > > > > > > > > Thank you. > > > > -- > > Adrian Klaver > > [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-04-21 05:33 UTC | newest] Thread overview: 7+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-16 21:51 Re: Does included columns part of the PK Igor Korot <[email protected]> 2026-03-16 21:53 ` Igor Korot <[email protected]> 2026-03-16 21:57 ` Adrian Klaver <[email protected]> 2026-03-16 23:38 ` Igor Korot <[email protected]> 2026-03-17 00:44 ` Adrian Klaver <[email protected]> 2026-03-17 01:11 ` Igor Korot <[email protected]> 2026-04-21 05:33 ` 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