Re: [GENERAL] What is the proper query - Mailing list pgsql-general

Not using the key_column_usage view.  What that view is doing is basically saying (my understanding from reading the docs, not testing it out):

CREATE TABLE tbl_pk

UNIQUE (col1, col2)​

CREATE TABLE tbl_fk

FOREIGN (col2, col1) REFERENCES tbl_pk (col1, col2)

Now your ordinal/position rows would be:

(1, 2)

(2, 1)

instead of:

(1, 1)

(2, 2)

if you had defined the FK and PK with the same column names in the same order, like is done almost always and like you did in your example.

If you want to enumerate constraints you need to use a different information_schema view or, as Melvin showed, use pg_catalog.  I'm not fluent enough to provide examples.  If you provide the question/problem you are trying to resolve others will likely offer suggestions.

David J.