I am currently working on a set of queries to pull data from a SQL table and then loop to pull any entries from other tables that are referenced in the first table through foreign keys.

aka if Table A column A can only have values that appear in Table B's primary key, I want to pull all rows of Table B referenced in my extract from Table A.

To do this in the past, I would have written a query that looks at information_schema.table_constraints and matched it against the key columns; Something like the suggested query in this article. However, when I pull the information from the table_constraints table in my current database, I get back an empty response; I get the table headers, but no rows. This is despite the fact that I know that there are many constraints, particularly foreign-key constraints, in the Postgresql database that I am using. The query giving me the empty response is as simple as possible, shown below:

SELECT * FROM information_schema.table_constraints

Is there somewhere else that I should be referencing to get the foreign key constraint information? How else can I find the foreign key constraints on a table?

EDIT: I am having a touch more luck finding things through pg_catalog; The data at least seems to exist in there. However, it is all abstracted as numerical IDs, and I am having a little trouble linking enough together to get to the actual column names and other key data.

CodePudding user response：

In the information_schema views you can only see objects for which you have permissions.

You are planning to do a join, not lots of little queries by primary key, right?