Postgres constraint query doing a cartesian join when multiple columns reference multiple columns

[ireneusz-ptak]

What happened

Trying to use DB schema exported to JSON from PG database, but the columns and referenced columns for FK constraint are duplicated (and unordered).

What you expected to happened

I'd expect columns and referenced columns arrays showing columns only once (and in the same order so you can make 1:1 relation between them)

What stack trace or error message from tbls did you see?

None

Anything else we need to know?

Nice project!

Environment

Latest tbls version
PostrgreSQL 14.15 on Debian

Solution?

Current query (https://github.com/k1LoW/tbls/blob/main/drivers/postgres/postgres.go#L651C1-L672C37) does a cartesian join when multiple columns in FK reference multiple columns in a referenced table.

Fixed query might look like that:

SELECT
cons.conname AS name,
CASE WHEN cons.contype = 't' THEN pg_get_triggerdef(trig.oid)
ELSE pg_get_constraintdef(cons.oid)
END AS def,
cons.contype AS type,
fcls.relname,
(SELECT ARRAY_AGG(attr.attname ORDER BY ARRAY_POSITION(cons.conkey, attr.attnum)) FROM pg_attribute AS attr WHERE attr.attrelid = cons.conrelid AND attr.attnum = ANY(cons.conkey)),
(SELECT ARRAY_AGG(fattr.attname ORDER BY ARRAY_POSITION(cons.confkey, fattr.attnum)) FROM pg_attribute AS fattr WHERE fattr.attrelid = cons.confrelid AND fattr.attnum = ANY(cons.confkey)),
descr.description AS comment
FROM pg_constraint AS cons
LEFT JOIN pg_trigger AS trig ON trig.tgconstraint = cons.oid AND NOT trig.tgisinternal
LEFT JOIN pg_class AS fcls ON cons.confrelid = fcls.oid
LEFT JOIN pg_description AS descr ON cons.oid = descr.objoid
WHERE
cons.conrelid = $1::oid
GROUP BY cons.conindid, cons.conname, cons.contype, cons.oid, trig.oid, fcls.relname, descr.description
ORDER BY cons.conindid, cons.conname