PostgreSQL. Error in getQueryInterface().describeTable() with column comments and schemas #17288
Open
3 of 6 tasks
Labels
pending-approval
Bug reports that have not been verified yet, or feature requests that have not been accepted yet
type: bug
Issue Creation Checklist
Bug Description
An error has been found in postgresQL if we use column comments in tables and have the same table name in different schemas.
The problem is when using the getQueryInterface().describeTable() function
Reproducible Example getQueryInterface().describeTable()
Here is the link to the SSCCE for this issue:
https://github.com/jsginacatalog/sequelize-sscce
What do you expect to happen?
Return the information of the table correctly.
What is actually happening?
The function thrown an exception:
error: more than one row returned by a subquery used as an expression.
The problem is in the query:
SELECT pk.constraint_type as "Constraint",c.column_name as "Field", c.column_default as "Default",c.is_nullable as "Null", (CASE WHEN c.udt_name = 'hstore' THEN c.udt_name ELSE c.data_type END) || (CASE WHEN c.character_maximum_length IS NOT NULL THEN '(' || c.character_maximum_length || ')' ELSE '' END) as "Type", (SELECT array_agg(e.enumlabel) FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid WHERE t.typname=c.udt_name) AS "special", (SELECT pgd.description FROM pg_catalog.pg_statio_all_tables AS st INNER JOIN pg_catalog.pg_description pgd on (pgd.objoid=st.relid) WHERE c.ordinal_position=pgd.objsubid AND c.table_name=st.relname) AS "Comment" FROM information_schema.columns c LEFT JOIN (SELECT tc.table_schema, tc.table_name, cu.column_name, tc.constraint_type FROM information_schema.TABLE_CONSTRAINTS tc JOIN information_schema.KEY_COLUMN_USAGE cu ON tc.table_schema=cu.table_schema and tc.table_name=cu.table_name and tc.constraint_name=cu.constraint_name and tc.constraint_type='PRIMARY KEY') pk ON pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name WHERE c.table_name = 'Foo' AND c.table_schema = 'public'
Missing cross by the schema name in the subquery to obtain the field "Comment"
AND C.TABLE_SCHEMA = st.schemaname
Environment
Would you be willing to resolve this issue by submitting a Pull Request?
Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.
The text was updated successfully, but these errors were encountered: