The Vertica Database Forums

Posted: **Fri May 16, 2014 4:00 pm**

Hi,

I know I can query the constraint_columns table to get a list of columns in my table constraint. However, there is no way to order the columns for each constraint in the position in which they were created.

Code: Select all

dbadmin=> create table c (c1 int, c2 int, c3 int, primary key (c1, c2, c3));
CREATE TABLE

dbadmin=> select constraint_name, column_name, constraint_type from constraint_columns where table_name = 'c' order by 1;
 constraint_name | column_name | constraint_type
-----------------+-------------+-----------------
 C_NOTNULL       | c1          | n
 C_NOTNULL       | c3          | n
 C_NOTNULL       | c2          | n
 C_PRIMARY       | c2          | p
 C_PRIMARY       | c1          | p
 C_PRIMARY       | c3          | p
(6 rows)

See how the column names for C_PRIMARY aren't in the order I created the PK?

Posted: **Mon May 19, 2014 2:43 am**

Try joining back to the COLUMNS system table. Something like this:

Code: Select all

select cc.constraint_name, cc.column_name, cc.constraint_type, c.ordinal_position
  from constraint_columns cc
  join columns c
    on c.table_id = cc.table_id
   and c.column_name = cc.column_name 
 where cc.table_name = 'c' order by 1, 4;

The Vertica Database Forums

How to order columns in constraint_columns table?

How to order columns in constraint_columns table?

Re: How to order columns in constraint_columns table?