On 02/26/2016 10:47 AM, David Binney wrote: > That is exactly the desired result, but in my db it is returning 2k rows > with exactly the same query, even filtered to a specific table.
Note to self, read the entire doc page:
http://www.postgresql.org/docs/9.5/interactive/information-schema.html " Note: When querying the database for constraint information, it is possible for a standard-compliant query that expects to return one row to return several. This is because the SQL standard requires constraint names to be unique within a schema, but PostgreSQL does not enforce this restriction. PostgreSQL automatically-generated constraint names avoid duplicates in the same schema, but users can specify such duplicate names.
This problem can appear when querying information schema views such as check_constraint_routine_usage, check_constraints, domain_constraints, and referential_constraints. Some other views have similar issues but contain the table name to help distinguish duplicate rows, e.g., constraint_column_usage, constraint_table_usage, table_constraints.
You are correct that the distinct will chomp the resultset down to the correct count, I am just concerned that there will be cases where it might not be accurate between the "rc" and the "kcu" joins as there is no table reference. I have simplified the query right down to just the join that i am unsure about. You can see below that as soon as i add the rc.unique_constraint_name, the distinct is no longer returning one row. In this case its fine because the rc values are the same and would distinct away, but there might be a case where they are diferent and you would have two rows and not know which values are correct?
David,
You are correct. Since referential_constraints lacks a table name you are hosed for this particular query - unless you ensure that your database is also standard compliant by not introducing duplicate constraint names within the same schema. If you cannot do that then the only solution to obtain a correct result is to use pg_catalog tables directly and bypass information_schema altogether.
