Hi, ALL,
draft=# SELECT * FROM information_schema.key_column_usage
[...]
There are 3 foreign keys in that table.
Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
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.
