Constraint Table in Postgresql Empty

Question

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.

Are you logged in as the right user? postgresql.org/docs/9.5/infoschema-table-constraints.html You could do a select count(*) to see if it has zero rows? — rogerdpack
– rogerdpack, Commented Feb 24, 2022 at 0:08
I tried a SELECT COUNT(*)... just then and got 0 as the return — AngusB
– AngusB, Commented Feb 24, 2022 at 0:11
Must have been permissions. Ended up solving it by copying the schema of the database, spinning up a new one with that schema, then querying that. — AngusB
– AngusB, Commented Feb 25, 2022 at 3:10

Laurenz Albe · Accepted Answer · 2022-02-24 06:52:57Z

2

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?

answered Feb 24, 2022 at 6:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AngusB Over a year ago

Yeah, a join; Want to pull all of the parent rows from all tables that a table has foreign key references to

Collectives™ on Stack Overflow

Constraint Table in Postgresql Empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related