postgres.email
❮

Does included columns part of the PK

  • Jump to comment-1
    Igor Korot<ikorot01@gmail.com>
    Mar 14, 2026, 7:09 AM UTC
    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,
    charactermaximumlength, characteroctetlength, numeric_precision,
    numericprecisionradix, numericscale, isnullable, column_default,
    CASE WHEN columnname IN (SELECT ccu.columnname FROM
    informationschema.constraintcolumn_usage ccu,
    informationschema.tableconstraints tc WHERE ccu.constraint_name =
    tc.constraintname AND tc.constrainttype = 'PRIMARY KEY' AND
    ccu.tablename = $2) THEN 'YES' ELSE 'NO' END AS ispk,
    ordinalposition FROM informationschema.columns col,
    informationschema.tableconstraints tc WHERE tc.table_schema =
    col.tableschema AND tc.tablename = col.table_name AND
    col.tableschema = $1 AND col.tablename = $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.
    View in PostgreSQL Archives →
    • Jump to comment-1
      Igor Korot<ikorot01@gmail.com>
      Mar 14, 2026, 7:13 AM UTC
      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"
      PYTHONSINGLETARGET="python311 -python310 -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 <ikorot01@gmail.com> 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 columnname, datatype,
      charactermaximumlength, characteroctetlength, numeric_precision,
      numericprecisionradix, numericscale, isnullable, column_default,
      CASE WHEN columnname IN (SELECT ccu.columnname FROM
      informationschema.constraintcolumn_usage ccu,
      informationschema.tableconstraints tc WHERE ccu.constraint_name =
      tc.constraintname AND tc.constrainttype = 'PRIMARY KEY' AND
      ccu.tablename = $2) THEN 'YES' ELSE 'NO' END AS ispk,
      ordinalposition FROM informationschema.columns col,
      informationschema.tableconstraints tc WHERE tc.table_schema =
      col.tableschema AND tc.tablename = col.table_name AND
      col.tableschema = $1 AND col.tablename = $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.
      View in PostgreSQL Archives →
    • Jump to comment-1
      David G. Johnston<david.g.johnston@gmail.com>
      Mar 14, 2026, 3:07 PM UTC
      On Saturday, March 14, 2026, Igor Korot <ikorot01@gmail.com> 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.
      View in PostgreSQL Archives →
    • Jump to comment-1
      Adrian Klaver<adrian.klaver@aklaver.com>
      Mar 14, 2026, 3:25 PM UTC
      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
      adrian.klaver@aklaver.com
      View in PostgreSQL Archives →
      • Jump to comment-1
        David G. Johnston<david.g.johnston@gmail.com>
        Mar 14, 2026, 3:51 PM UTC
        On Saturday, March 14, 2026, Adrian Klaver <adrian.klaver@aklaver.com>
        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.
        View in PostgreSQL Archives →
        • Jump to comment-1
          Igor Korot<ikorot01@gmail.com>
          Mar 14, 2026, 4:57 PM UTC
          Hi,
          On Sat, Mar 14, 2026 at 8:51 AM David G. Johnston <
          david.g.johnston@gmail.com> wrote:
          On Saturday, March 14, 2026, Adrian Klaver <adrian.klaver@aklaver.com>
          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.
          View in PostgreSQL Archives →
          • Jump to comment-1
            Adrian Klaver<adrian.klaver@aklaver.com>
            Mar 14, 2026, 11:30 PM UTC
            On 3/14/26 9:56 AM, Igor Korot wrote:
            Hi,
            On Sat, Mar 14, 2026 at 8:51 AM David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
            On Saturday, March 14, 2026, Adrian Klaver
            <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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-columns-table:
            1 TABLE_CAT VARCHAR(128) This is always null.
            2 TABLESCHEM VARCHAR(128) The name of the schema containing TABLENAME.
            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
            adrian.klaver@aklaver.com
            View in PostgreSQL Archives →
            • Jump to comment-1
              Igor Korot<ikorot01@gmail.com>
              Mar 15, 2026, 6:24 AM UTC
              Hi, Adrian,
              On Sat, Mar 14, 2026 at 6:30 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

              On 3/14/26 9:56 AM, Igor Korot wrote:
              Hi,

              On Sat, Mar 14, 2026 at 8:51 AM David G. Johnston
              <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:

              On Saturday, March 14, 2026, Adrian Klaver
              <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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-columns-table:

              1 TABLE_CAT VARCHAR(128) This is always null.
              2 TABLESCHEM VARCHAR(128) The name of the schema containing TABLENAME.
              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 pgcatalog.pgattribute ta ... "


              case 2

              select ta.attname, ia.attnum, ic.relname, n.nspname, NULL"
              " from pgcatalog.pgattribute 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
              adrian.klaver@aklaver.com
              View in PostgreSQL Archives →