Identify a Tables Primary Key Columns

Jim_Knicely · March 2019

The Vertica system table PRIMARY_KEYS lists the columns that compose a tables primary key.

Example:

dbadmin=> \dS primary_keys
                            List of tables
  Schema   |     Name     |  Kind  |       Description       | Comment
-----------+--------------+--------+-------------------------+---------
v_catalog | primary_keys | system | Primary key information |
(1 row)

dbadmin=> SELECT column_name, ordinal_position
dbadmin->   FROM primary_keys
dbadmin->  WHERE table_name = 'test_table';
column_name | ordinal_position
-------------+------------------
a           |                1
b           |                2
c           |                3
d           |                4
e           |                5
(5 rows)

You can use the LISTAGG function to group the columns of a composite key into a single record!

dbadmin=> SELECT listagg(column_name) pk
dbadmin->   FROM primary_keys
dbadmin->  WHERE table_name = 'test_table';
    pk
-----------
a,b,c,d,e
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/PRIMARY_KEYS.htm

Have fun!