Hello Tatsuro,
On 2026-Jan-16, Tatsuro Yamada wrote:
> As you pointed out, it was indeed inappropriate to exclude some types of
> constraints while using the title "list constraints."
> To address this issue, I have modified the query to include domain
> constraints in the output. This is included in the v5 patch.
>
> The revised query is shown below.
> The main difference from the v4 patch is that the pg_type table is now
> joined.
I wonder if the double LEFT JOIN is best, or you should instead join
pg_constraint twice and UNION ALL them, like
SELECT n.nspname AS "Schema",
cns.conname AS "Name" ,
pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition",
cns.relname AS "Table"
FROM ( select cns1.*, NULL as relname from pg_catalog.pg_constraint AS cns1
JOIN pg_catalog.pg_type t ON t.oid = cns1.contypid
union all
select cns2.*, c.relname from pg_catalog.pg_constraint AS cns2
JOIN pg_catalog.pg_class c on c.oid = cns2.conrelid
) AS cns
JOIN pg_catalog.pg_namespace n ON n.oid = cns.connamespace
WHERE n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4 ;
I think knowing what's best is going to require testing with a large
number of constraints to see which one scales best. (The million table
challenge!).
Also, if you're going to show type constraints, I think you should show
the type name.
Tom mentioned the issue of sort order. So there are two things, one is
where the column appears. You have
Schema │ Name │ Definition │ Table
and he suggested
Schema │ Table | Name │ Definition
now if we add the type, we could do
Schema │ Table | Type | Name │ Definition
I kinda agree that Definition, being potentially arbitrarily long,
should go at the end. Not sure about where to put constraint name vs.
table name. The other ordering issue is the ORDER BY clause -- makes me
wish we had a way to choose whether to sort by table/type name or by
constraint name.
I like the idea of distinguishing filter spec from the base command name
using upper/lowercase -- the "\dCN[cfnptue]" part.
I agree with another reviewer that said that having exec_command_d()
check only the third char is kinda pointless. Just let
listConstraints() check everything seems easiest and more consistent.
I don't think making the Table column be conditional on whether + is
given is useful. I mean, what can do you with a constraint list if you
don't know where each applies?
There should also be a "Constraint type" column (check, FK, etc), if
more than one type of constraints is displayed. Perhaps that column
disappears if you have the + option (since it would be redundant with
the definition).
I was thinking the pattern system is too simplistic. But after looking
again, I think the problem is that your tests don't include more
complicated cases like listing constraints in matching schemas and
matching tables, something like
\dCN cust*.order*
(list constraints in schemas matching cust* and tables matching order*)
Regards
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Finally, the phrase, 'No one was ever fired for buying an IBM' I don't believe
has ever been translated into German." (Leonard Tramiel)
