Thanks, Tom.
As I hinted at the beginning, it wasn't *difficult* to just use the pg_catalog-based query and use regexp_match() to pull out the relevant parts I needed from the foreign-key description. It's just that I started with the other query since it seemed to already offer the columns I wanted; and when I started digging into why it wasn't working, the inconsistency rubbed me the wrong way.
For sure, though, it's not our/your job to fix inconsistencies in the SQL spec itself.
Kirk Parker <khp@equatoria.us> writes:
> Tom Lane's answer makes sense, but I can't see where the permissions are
> lacking--the user seems to have all needed rights on all the relevant
> tables (and the same as the DB owner, for that matter.)
[ looks closer... ] constraint_column_usage has a tighter filter than
I would have guessed:
\d+ information_schema.constraint_column_usage
...
View definition:
...
WHERE pg_has_role(x.tblowner, 'USAGE'::text);
So you have to actually *be* the table owner, or at least have been
GRANTed that role, in order to see entries about the table in it.
This seems to match what it says in the spec, but I have to confess
bafflement as to why they made this one more restrictive than
either table_constraints or key_column_usage.
regards, tom lane