Identify a Tables Primary Key Columns
Jim_Knicely
- Select Field - Administrator
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!
0