Re: [PATCH] psql: add \dcs to list all constraints

Hi Alvaro,

Thank you for your comments.

On Sat, Jan 17, 2026 at 1:01 AM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
>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!).

I tested this with one million entries, and the query you proposed was
more than 1,000 times faster. Given this significant difference in
execution time, I will modify the query to use UNION ALL.
If you are interested in the benchmark results, please refer to the
attached test_result.txt.

>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 agree that the constraint type (e.g., NOT NULL, CHECK, etc.) should be
displayed. Initially, I thought the type could be inferred from the name,
but I now agree that it is better to display it explicitly, even without
the + option since it may not always be possible to determine the type
reliably from the name.
This change will be included in the next patch.

Regarding the column order (e.g., whether the constraint name or table name
should come first), I am starting to think that since constraints are
always
associated with a table or a domain (please correct me if I am mistaken),
it makes sense to show the table (or domain) name first.

For now, I will use the following column layout:

Schema | Table | Type | Name | Definition

As for making the ORDER BY configurable, I will not implement that for now.
There are no similar examples in other meta-commands, it would require
introducing a new option, and it does not seem like a critical feature at
this point. The sorting will follow the column order shown above.

>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.

As I mentioned in a previous email, the reason exec_command_d() only
checks the third character is that it follows the same implementation
pattern as other existing commands.

While I agree that this could be improved, since the current behavior is
consistent with other commands, I do not consider it critical at the
moment.
If I were to change it, I would likely postpone that improvement to a later
patch.

>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 see.

>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*)

You're right. That test case was missing. I will add it.

# Summary
The following changes are planned for the next patch:

- Changed the query (using UNION ALL)
- Changed the columns and their order (and the sort order accordingly):
- Schema | Table | Type | Name | Definition
- Toggle definition verbosity with the + option
- Added a test case: \dCN cust*.order*

The following items will not be included for now (as they are not critical):

- Option to switch sort order
(e.g., sort by constraint name vs. table name)
- Improved command name checking logic

Regards,
Tatsuro Yamada