Re: BUG #1006: information schema constraint information. - Mailing list pgsql-bugs

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> [ information_schema.constraint_column_usage gives wrong answers ]

I think this part of the view's definition:

            AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
                      ELSE c.conkey[pos.n] = a.attnum END)

should just be

            AND c.conkey[pos.n] = a.attnum

The confkey array shows column numbers of the referenced columns, which
is not the right thing to look at.

If the view were also supposed to show referenced columns, then I think
we'd need an additional UNION arm that joined on confrelid and confkey[]
instead of conrelid/conkey[].  But if I read SQL99 correctly, only
referencing not referenced columns are supposed to be shown.

BTW, I also recommend deleting the clause

            AND a.attnum > 0

since for instance a UNIQUE constraint on the OID column is legitimate.

Peter, does this change look right to you?

            regards, tom lane