BUG #16642: INFORMATION_SCHEMA-DESIGN

The following bug has been logged on the website:

Bug reference: 16642
Logged by: YONGPENG SU
Email address: super19990310(at)163(dot)com
PostgreSQL version: 12.4
Operating system: WIN10
Description:

Three table constraint_column_usage, key_column_usage and table_constrants
in information_schema describe all constraints of a table and it's column,
especially FOREIGN KEY constraints. A widely-used QUERY "A Primary Key of a
table is refferenced by other table and it's columns", we can esaily join
the the table mentioned above by "constriant_name" equaled to finish SQL.
But what's new above PG11, also in PG12 is Partition-Table Definition. The
Parent can define PK and FK constraints, and its Sub-Partition Table
inherits the FK constraints and has the same FK constraints name with the
parent, which causes the FK constraint name can't identify a FK constraint
and it is ambiguous in the literal meaning. in this case(Parent Partition
Table and Sub-Partition Table have the same FK constraint name), we can only
identify its id by the table name and constraint name. so can their be
represented more column to express the reation cleatly.
eg. information_schema.constraint_column_usage describe A table's id(PK) is
refferenced by constraints(constraint_name)， can it add a column table_name
matched with constraint_name to identify a constraint. or the join by
constraint_name(if there are many sub-partition table of a table means many
same constraint_name. the join will be much slower, the join by the
constraint_name is not 1-1, becomes 1-n to multiple each row, the total
rows becomes n*n*n). Here is my practice, looking forward to your
improvement.