How to order columns in constraint_columns table?

[Josh]

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?

[JimKnicely]

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;