Re: What is the proper query

On Mon, Aug 21, 2017 at 9:08 PM, Igor Korot <ikorot01(at)gmail(dot)com> wrote:

> 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.