Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Identify a Tables Primary Key Columns

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


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';
(1 row)

Helpful Link:

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.