Malformed query repeats one field multiple times

[datanymizer]

Malformed query repeats one field multiple times

[mbeynon]

The problem here was there can be multiple rows in pg_catalog.pg_type field typname that join with information_schema.columns field udt_name. In my case there was three, because they were defined in different namespaces (schemas) in the same db.

Solution is to also join on pg_catalog.pg_namespace to ensure we have the right one. After this change to schema_inspector.rs, it returns just the 30 fields and dumps properly.

Commented out the old query and added the new one:

// const TABLE_COLUMNS_QUERY: &str = "SELECT cc.column_name, cc.ordinal_position, cc.data_type, pt.oid
// FROM information_schema.columns as cc
// JOIN pg_catalog.pg_type as pt
// ON cc.udt_name = pt.typname
// WHERE cc.table_schema = $1 and cc.table_name = $2
// ORDER BY cc.ordinal_position ASC";
const TABLE_COLUMNS_QUERY: &str = "SELECT cc.column_name, cc.ordinal_position, cc.data_type, pt.oid
FROM information_schema.columns as cc
JOIN pg_catalog.pg_namespace as pn
ON cc.udt_schema = pn.nspname
JOIN pg_catalog.pg_type as pt
ON cc.udt_name = pt.typname AND pn.oid = pt.typnamespace
WHERE cc.table_schema = $1 and cc.table_name = $2
ORDER BY cc.ordinal_position ASC";

PR will be coming with this fix.