Identify a Table’s Primary Key Columns

Posted March 7, 2019 by James Knicely, Vertica Field Chief Technologist

The Vertica system table PRIMARY_KEYS lists the columns in a table’s 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