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

Hi Tatsuro

On 31/10/2025 16:19, Tatsuro Yamada wrote:

Please find the attached file.
Feedback and suggestions are very welcome.

Thanks for the patch!

I've been playing with a few edge cases and everything seems to work
just fine. Bellow I am listing the cases I tested, so that you can take
a look if some of them should be included in the regression tests - not
sure if it is necessary, since this feature is only reading the
constraint definitions from the catalog.

CREATE TABLE zoo (
cage int,
animal text,
is_aggressive boolean,
CONSTRAINT no_different_animals_in_same_cage
EXCLUDE USING gist (
cage WITH =,
animal WITH <>,
int4(is_aggressive) WITH <>
)
);
CREATE TABLE

postgres=# \dcs+
List of constraints
Schema | Name |
Definition | Table
--------+-----------------------------------+-------------------------------------------------------------------------------+-------
public | no_different_animals_in_same_cage | EXCLUDE USING gist (cage
WITH =, animal WITH <>, int4(is_aggressive) WITH <>) | zoo
(1 row)

postgres=# CREATE TABLE zoo_partial (
cage int,
animal text,
is_aggressive boolean,
CONSTRAINT zoo_partial_excl
EXCLUDE USING gist (
cage WITH =,
animal WITH <>
)
WHERE (is_aggressive)
);
CREATE TABLE
postgres=# \dcs+ zoo_partial*
List of constraints
Schema | Name | Definition
| Table
--------+------------------+------------------------------------------------------------------------+-------------
public | zoo_partial_excl | EXCLUDE USING gist (cage WITH =, animal
WITH <>) WHERE (is_aggressive) | zoo_partial
(1 row)

postgres=# CREATE TABLE zoo_deferrable (
cage int,
animal text,
CONSTRAINT zoo_deferrable_excl
EXCLUDE USING gist (
cage WITH =,
animal WITH <>
)
DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE
postgres=# \dcs+ zoo_deferrable_excl
List of constraints
Schema | Name |
Definition | Table
--------+---------------------+--------------------------------------------------------------------------------+----------------
public | zoo_deferrable_excl | EXCLUDE USING gist (cage WITH =, animal
WITH <>) DEFERRABLE INITIALLY DEFERRED | zoo_deferrable
(1 row)

postgres=# CREATE TABLE zoo_expr (
cage int,
animal text,
is_aggressive boolean,
CONSTRAINT zoo_expr_excl
EXCLUDE USING gist (
cage WITH =,
lower(animal) WITH =,
int4(is_aggressive) WITH <>
)
);
CREATE TABLE
postgres=# \dcs+ zoo_expr_excl
List of constraints
Schema | Name | Definition
| Table
--------+---------------+-------------------------------------------------------------------------------------+----------
public | zoo_expr_excl | EXCLUDE USING gist (cage WITH =, lower(animal)
WITH =, int4(is_aggressive) WITH <>) | zoo_expr
(1 row)

postgres=# CREATE TABLE zoo_check (
cage int,
animal text,
is_aggressive boolean
);

ALTER TABLE zoo_check
ADD CONSTRAINT zoo_check_aggr
CHECK (is_aggressive IS NOT NULL)
NOT VALID;
CREATE TABLE
ALTER TABLE
postgres=# \dcs+ zoo_check_aggr
List of constraints
Schema | Name | Definition
| Table
--------+----------------+-----------------------------------------------+-----------
public | zoo_check_aggr | CHECK ((is_aggressive IS NOT NULL)) NOT VALID
| zoo_check
(1 row)

postgres=# CREATE TABLE zoo_parent (
cage int PRIMARY KEY
);

CREATE TABLE zoo_child (
animal text
) INHERITS (zoo_parent);
CREATE TABLE
CREATE TABLE
postgres=# \dcs+ zoo_parent_cage_not_null
List of constraints
Schema | Name | Definition | Table
--------+--------------------------+---------------+------------
public | zoo_parent_cage_not_null | NOT NULL cage | zoo_parent
public | zoo_parent_cage_not_null | NOT NULL cage | zoo_child
(2 rows)

CREATE TABLE zoo_part (
cage int,
animal text,
CONSTRAINT zoo_part_pk PRIMARY KEY (cage)
) PARTITION BY RANGE (cage);
CREATE TABLE
db=# CREATE TABLE zoo_part_1
PARTITION OF zoo_part
FOR VALUES FROM (0) TO (100);
CREATE TABLE
db=# \dcs+
List of constraints
Schema | Name | Definition | Table
--------+------------------------+--------------------+------------
public | zoo_part_1_pkey | PRIMARY KEY (cage) | zoo_part_1
public | zoo_part_cage_not_null | NOT NULL cage | zoo_part
public | zoo_part_cage_not_null | NOT NULL cage | zoo_part_1
public | zoo_part_pk | PRIMARY KEY (cage) | zoo_part
(4 rows)

postgres=# drop table zoo_drop ;
DROP TABLE
postgres=# CREATE TABLE zoo_drop (
cage int,
animal text,
CONSTRAINT zoo_drop_uq UNIQUE (cage, animal)
);
CREATE TABLE
postgres=# \dcs+
List of constraints
Schema | Name | Definition | Table
--------+-------------+-----------------------+----------
public | zoo_drop_uq | UNIQUE (cage, animal) | zoo_drop
(1 row)

postgres=# ALTER TABLE zoo_drop DROP COLUMN animal;
ALTER TABLE
postgres=# \dcs+
List of constraints
Schema | Name | Definition | Table
--------+------+------------+-------
(0 rows)

One nitpick: the order of the constraints is different from the one in \d+:

postgres=# CREATE TABLE t (
c text NOT NULL,
b text NOT NULL,
a text NOT NULL
);
CREATE TABLE
postgres=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
c | text | | not null | | extended |
| |
b | text | | not null | | extended |
| |
a | text | | not null | | extended |
| |
Not-null constraints:
"t_c_not_null" NOT NULL "c"
"t_b_not_null" NOT NULL "b"
"t_a_not_null" NOT NULL "a"
Access method: heap

postgres=# \dcs+ t*
List of constraints
Schema | Name | Definition | Table
--------+--------------+------------+-------
public | t_a_not_null | NOT NULL a | t
public | t_b_not_null | NOT NULL b | t
public | t_c_not_null | NOT NULL c | t
(3 rows)

For consistency, it would be nice to have both options listing in the
same order, but in case it would mean adding too much complexity to the
code, I'd say it is just fine as-is.

Thanks!

Best, Jim

Hi Jim,

Thank you for your review.

On Sat, Jan 3, 2026 at 4:51 AM Jim Jones <jim.jones@uni-muenster.de> wrote:

I've been playing with a few edge cases and everything seems to work
just fine. Bellow I am listing the cases I tested, so that you can take
a look if some of them should be included in the regression tests - not
sure if it is necessary, since this feature is only reading the
constraint definitions from the catalog.

The original regression tests only covered simple cases, so the more
complex edge cases you tried were very helpful.

The method for converting constraints into definition strings in \dcs uses
the same function as \d (i.e., pg_catalog.pg_get_constraintdef), so I don’t
think there are any major issues there.

One nitpick: the order of the constraints is different from the one in \d+:
For consistency, it would be nice to have both options listing in the
same order, but in case it would mean adding too much complexity to the
code, I'd say it is just fine as-is.

Regarding the constraint display order, I hadn’t noticed that it differed
between \d and \dcs. Thanks for pointing that out.
In \dcs, constraints are sorted in ascending order by both schema name
and constraint name.
On the other hand, \d appears to use the column number as one of the
sorting keys. I believe this difference explains the discrepancy in the
display order.
As you suggested, I’d like to keep the current behavior to avoid adding
unnecessary complexity.

The next patch will include the following:
- Rebased version
- Expanded regression tests (kept to a minimum)

Regards,
Tatsuro Yamada

Hi,

On Sat, Jan 10, 2026 at 12:14 PM Tatsuro Yamada <yamatattsu@gmail.com>
wrote:

The next patch will include the following:
- Rebased version
- Expanded regression tests (kept to a minimum)

I revised the patch based on my previous email.

It includes:
- Rebased version
- Cosmetic changes
- Add incorrect option check
- Use validateSQLNamePattern() instead of processSQLNamePattern()
- Fix bug \dcs with 'x' (expanded display) option
- Add table name into ORDER BY to get consistent results
- Add regression test cases
- some edge cases suggested by Jim (Thanks!)
- test cases for using validateSQLNamePattern()
- incorrect option check, and so on

Comments and suggestions are welcome.

Regards,
Tatsuro Yamada

On 12/01/2026 06:39, Tatsuro Yamada wrote:

Comments and suggestions are welcome.

Here a few comments to v2:

== listConstraints() ==

...

if (!showSystem && !pattern)
appendPQExpBufferStr(&buf,
"WHERE n.nspname <> 'pg_catalog' \n"
" AND n.nspname <> 'information_schema' \n");

if (!validateSQLNamePattern(&buf, pattern,
!showSystem && !pattern, false,
"n.nspname", "cst.conname", NULL,
"pg_catalog.pg_table_is_visible(cst.conrelid)",
NULL, 3))
{
termPQExpBuffer(&buf);
return false;
}

if (!showAllkinds)
{
appendPQExpBufferStr(&buf, " AND cst.contype in ("); <== here!

....

It looks like that a WHERE condition can be potentially added to the "if
(!showAllkinds)" block even if there is no WHERE clause at all. I'm not
sure if this path is even possible, but perhaps a more defensive
approach here wouldn't be a bad idea, e.g.

...
bool have_where = false;

if (!showSystem && !pattern)
{
appendPQExpBufferStr(&buf,
"WHERE n.nspname <> 'pg_catalog' \n"
" AND n.nspname <> 'information_schema' \n");
have_where = true;
}

if (!validateSQLNamePattern(&buf, pattern,
have_where, false,
"n.nspname", "cst.conname", NULL,
"pg_catalog.pg_table_is_visible(cst.conrelid)",
&have_where, 3))
{

if (!showAllkinds)
{
appendPQExpBuffer(&buf, " %s cst.contype in (",
have_where ? "AND" : "WHERE");
...

What do you think?

== Patch name ==

It'd be better if you format your patch name with the version upfront, e.g.

$ git format-patch -1 -v3

I've tried a few more edge cases and so far everything is working as
expected

postgres=# \set ECHO_HIDDEN on

postgres=# CREATE TABLE zoo (id int PRIMARY KEY, name text);
ALTER TABLE zoo ADD CONSTRAINT 🐘1 CHECK (name = '🐘');
CREATE TABLE
ALTER TABLE
postgres=# \dcs 🐘*
/******** QUERY *********/
SELECT n.nspname AS "Schema",
cst.conname AS "Name"
FROM pg_catalog.pg_constraint cst
JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE cst.conname OPERATOR(pg_catalog.~) '^(🐘.*)$' COLLATE
pg_catalog.default
AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2;
/************************/

List of constraints
Schema | Name
--------+------
public | 🐘1
(1 row)

postgres=# \dcs+ 🐘*
/******** QUERY *********/
SELECT n.nspname AS "Schema",
cst.conname AS "Name" ,
pg_catalog.pg_get_constraintdef(cst.oid) AS "Definition",
c.relname AS "Table"
FROM pg_catalog.pg_constraint cst
JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE cst.conname OPERATOR(pg_catalog.~) '^(🐘.*)$' COLLATE
pg_catalog.default
AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2, 4;
/************************/

List of constraints
Schema | Name | Definition | Table
--------+------+-----------------------------+-------
public | 🐘1 | CHECK ((name = '🐘'::text)) | zoo
(1 row)

postgres=# \dcs
/******** QUERY *********/
SELECT n.nspname AS "Schema",
cst.conname AS "Name"
FROM pg_catalog.pg_constraint cst
JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2;
/************************/

List of constraints
Schema | Name
--------+-----------------
public | zoo_id_not_null
public | zoo_pkey
public | 🐘1
(3 rows)

Thanks

Best, Jim

Hi Jim,

Thanks for your review comments!

On Tue, Jan 13, 2026 at 12:17 AM Jim Jones <jim.jones@uni-muenster.de>
wrote:

Here a few comments to v2:

== listConstraints() ==

It looks like that a WHERE condition can be potentially added to the "if

(!showAllkinds)" block even if there is no WHERE clause at all. I'm not
sure if this path is even possible, but perhaps a more defensive
approach here wouldn't be a bad idea, e.g.

...
bool have_where = false;

if (!showSystem && !pattern)
{
appendPQExpBufferStr(&buf,
"WHERE n.nspname <> 'pg_catalog' \n"
" AND n.nspname <> 'information_schema' \n");
have_where = true;
}

if (!validateSQLNamePattern(&buf, pattern,
have_where, false,
"n.nspname", "cst.conname", NULL,
"pg_catalog.pg_table_is_visible(cst.conrelid)",
&have_where, 3))
{

if (!showAllkinds)
{
appendPQExpBuffer(&buf, " %s cst.contype in (",
have_where ? "AND" : "WHERE");
...

What do you think?

Based on the value passed to validateSQLNamePattern(), one or more of
n.nspname, cst.conname, or pg_catalog.pg_table_is_visible(cst.conrelid) is
added to the query string together with either WHERE or AND.
Therefore, I believe there is no case in which the if (!showAllkinds) block
is
reached without an existing WHERE clause.
If you are aware of a specific test case where this can happen, I would
appreciate it if you could share it with me.

For now, my conclusion is that I would like to keep this part as it is. I
apologize
if I have missed something.

== Patch name ==

It'd be better if you format your patch name with the version upfront, e.g.

$ git format-patch -1 -v3

Thank you for the suggestion. From now on, I will generate patches using
the options you mentioned.

I've tried a few more edge cases and so far everything is working as

expected

postgres=# \set ECHO_HIDDEN on

postgres=# \dcs 🐘*
/******** QUERY *********/
...

FROM pg_catalog.pg_constraint cst

JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
JOIN pg_catalog.pg_class c on c.oid = cst.conrelid

Thank you for testing these additional edge cases.
I noticed that when the "+" (verbose option) is not used, the table name is
not needed. In that case, joining the pg_class table is unnecessary.
This will be fixed in the next patch.

Thanks,
Tatsuro Yamada

Tatsuro Yamada <yamatattsu@gmail.com> writes:

I noticed that when the "+" (verbose option) is not used, the table name is
not needed. In that case, joining the pg_class table is unnecessary.

A couple of drive-by comments:

* I don't like the name "\dcs": "cs" is a very strange choice of
abbreviation for "constraint". Now, psql already has "\dc" and
"\dconfig", so our options are pretty limited, but I think that
"\dcn" would be least surprising. It's typical to drop vowels
when abbreviating, so dropping the "o" makes sense (and we'd be
too close to "\dconfig" if we don't). But I don't see the
argument for skipping "n" to seize on the next consonant "s".

* You might do well to use upper-case letters for add-on
filters (that is, "N" not "n" for not-null, etc). This isn't
really a convention we've used elsewhere, except for the case
of "S" for "system" in some commands. But I think it's too
hard to tell the base command name from the filter add-ons
without some typographic help like that. Also, with this
being next door to "\dconfig" which is often abbreviated as
"\dcon", it seems like "\dcsn" or "\dcnn" would look too much
like typos for that.

* What about constraints on types (primarily, domain check
constraints)? I'm not insisting that this patch support that,
but it'd be smart to take some thought now for how a future
command for that could be wedged into the command namespace.

* Not sure about column order of the output:

Schema | Name | Definition | Table
--------+----------------------------+----------------------+-------

I find that just really odd. Why not "Schema | Table | Name |
Definition"? This also touches on whether we really need a "+" form
at all, rather than just always printing all four columns, because
I'm not sure I buy that you can infer the table name from the
constraint name. Yes, it probably works okay for constraints that
were given default names, but for user-specified names maybe not so
much. There's also the point that we don't guarantee that constraint
names are unique per-schema, only per-table.

regards, tom lane

I wrote:

* You might do well to use upper-case letters for add-on
filters (that is, "N" not "n" for not-null, etc). This isn't
really a convention we've used elsewhere, except for the case
of "S" for "system" in some commands. But I think it's too
hard to tell the base command name from the filter add-ons
without some typographic help like that.

Or we could invert that: make the base command name "\dCN"
and then the add-on filters stay lower case. Since we
already have, eg, "\dC", this might be less of a departure
from existing practice.

regards, tom lane

Hi Tom,

Thanks for your comments!

A couple of drive-by comments:

* I don't like the name "\dcs": "cs" is a very strange choice of
abbreviation for "constraint". Now, psql already has "\dc" and
"\dconfig", so our options are pretty limited, but I think that
"\dcn" would be least surprising. It's typical to drop vowels
when abbreviating, so dropping the "o" makes sense (and we'd be
too close to "\dconfig" if we don't). But I don't see the
argument for skipping "n" to seize on the next consonant "s".

As you mentioned, there are already meta-command names such as
\dc, \dC, and \dconfig, so I wanted \dcs to be as distinct as possible.
That is why I formed it using the first letters of each syllable.

That said, I do not have a strong preference for \dcs, so I think
it is perfectly fine to change it to \dcn.

* You might do well to use upper-case letters for add-on
filters (that is, "N" not "n" for not-null, etc). This isn't
really a convention we've used elsewhere, except for the case
of "S" for "system" in some commands. But I think it's too
hard to tell the base command name from the filter add-ons
without some typographic help like that. Also, with this
being next door to "\dconfig" which is often abbreviated as
"\dcon", it seems like "\dcsn" or "\dcnn" would look too much
like typos for that.

Regarding the suggestion to use uppercase letters for add-on filters,
I agree that this makes it easier to distinguish between the base
meta-command name and the filter options. However, as you pointed out,
there is not much precedent for this.

For example, I used \df as a reference, which has add-on filters
like this:

\df[anptw][Sx+]...

Currently, \dcs also uses lowercase letters, but I would be open to
switching to uppercase if that is acceptable. (It might also be worth
considering changing \df to uppercase at some point.)

* What about constraints on types (primarily, domain check
constraints)? I'm not insisting that this patch support that,
but it'd be smart to take some thought now for how a future
command for that could be wedged into the command namespace.

I understand that type constraints are constraints within the domain,
but since a dedicated meta command (\dD) already existed, they were
excluded from \dcs. \dD displays check constraints, etc., as shown below.

I understand that type constraints are constraints within domains.
Since there is already a dedicated meta-command (\dD) for domains,
I initially excluded them from \dcs.
As shown below, \dD already displays check constraints and related
information:

# \dD
List of domains
Schema | Name | Type | Collation | Nullable | Default | Check
--------+------+------+-----------+----------+---------+-------

At first, I thought it would be better to avoid this because it
overlaps with \dD, but now I feel like it's okay to include it if
\dcs is a command that covers all constraints.
Is it okay to include it in \dcs?

At first, I thought it would be better to avoid overlapping with
\dD. However, I am now thinking that it might be acceptable to
include them if \dcs is meant to be a command that covers all constraints.

Would it be okay to include domain-related constraints in \dcs?

* Not sure about column order of the output:

Schema | Name | Definition | Table
--------+----------------------------+----------------------+-------

I find that just really odd. Why not "Schema | Table | Name |
Definition"? This also touches on whether we really need a "+" form
at all, rather than just always printing all four columns, because
I'm not sure I buy that you can infer the table name from the
constraint name. Yes, it probably works okay for constraints that
were given default names, but for user-specified names maybe not so
much. There's also the point that we don't guarantee that constraint
names are unique per-schema, only per-table.

There are different possible opinions about the column order in \dcs.
I considered two main approaches: listing constraints by table, or
listing them by constraint name.
In the current implementation, I chose the latter, based on the assumption
that the user's primary interest in this command is the constraint name.

For example, although the \di command can display indexes on a per-table
basis, it primarily presents them as a list organized by index name.
The Schema column is followed by the index name, and the output is sorted
by that name, as shown below:

# \di
List of indexes
Schema | Name | Type | Owner | Table
--------+-----------+-------+----------+-------
public | bar_pkey | index | postgres | bar
public | foo_pkey | index | postgres | foo
public | hoge_pkey | index | postgres | hoge
public | temp_ind1 | index | postgres | bar
(4 rows)

I applied the same design principle to \dcs. Also, users can use the
\d command to view constraints on a table-by-table basis.
For that reason, I would like to keep the column order as it is.
What do you think?

Regards,
Tatsuro Yamada

Hi Tom,

On Thu, Jan 15, 2026 at 12:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:

* You might do well to use upper-case letters for add-on
filters (that is, "N" not "n" for not-null, etc). This isn't
really a convention we've used elsewhere, except for the case
of "S" for "system" in some commands. But I think it's too
hard to tell the base command name from the filter add-ons
without some typographic help like that.

Or we could invert that: make the base command name "\dCN"
and then the add-on filters stay lower case. Since we
already have, eg, "\dC", this might be less of a departure
from existing practice.

That sounds like a good idea.
Keeping the add-on filters in lowercase will help maintain
consistency with other commands.
Therefore, I will change the command name to the one you suggested.

The next patch will include the following changes:

- Rename the command from \dcs to \dCN (proposed by Tom. Thanks!)
- Join pg_class in the query only when the "+" option is used
(identified through Jim's additional testing. Thanks!)

Note: The following two points, which were discussed in the previous email,
will be addressed once the discussion is settled:

- Changing the column order displayed by the \dCN command
- Adding domain constraints to the list of displayed items

Thanks,
Tatsuro Yamada

Show quoted text

Hi Tom and Jim,

The next patch will include the following changes:

- Rename the command from \dcs to \dCN (proposed by Tom. Thanks!)
- Join pg_class in the query only when the "+" option is used
(identified through Jim's additional testing. Thanks!)

I have prepared a new patch and am sending it here.
Please find the attached file.

Note: The following two points, which were discussed in the previous

email,

will be addressed once the discussion is settled:

- Changing the column order displayed by the \dCN command
- Adding domain constraints to the list of displayed items

I have shared my thoughts on the two points raised by Tom in the email
below, and I would appreciate your comments.

/messages/by-id/CAOKkKFuYfdvpQ7eSYWxB1YrzwVafWm++ctXBPe_Rb1YqeOKjjA@mail.gmail.com

Regards,
Tatsuro Yamada

On Jan 15, 2026, at 14:32, Tatsuro Yamada <yamatattsu@gmail.com> wrote:

Hi Tom and Jim,

The next patch will include the following changes:

- Rename the command from \dcs to \dCN (proposed by Tom. Thanks!)
- Join pg_class in the query only when the "+" option is used
(identified through Jim's additional testing. Thanks!)

I have prepared a new patch and am sending it here.
Please find the attached file.

Note: The following two points, which were discussed in the previous email,
will be addressed once the discussion is settled:

- Changing the column order displayed by the \dCN command
- Adding domain constraints to the list of displayed items

I have shared my thoughts on the two points raised by Tom in the email
below, and I would appreciate your comments.

/messages/by-id/CAOKkKFuYfdvpQ7eSYWxB1YrzwVafWm++ctXBPe_Rb1YqeOKjjA@mail.gmail.com

Regards,
Tatsuro Yamada
<v3-0001-Add-list-constraints-meta-command-dCN-on-psql.patch>

Hi Tatsuro-san,

Thanks for the patch. I just reviewed the patch, and I think it is solid already, just got a few small comments:

1 - commit message
```
\dCN shows all kind of constraints by using pg_constraint.
```

All kind -> all kinds

2.
```
+					switch (cmd[3])
+					{
+						case '\0':
+						case '+':
+						case 'S':
+						case 'c':
+						case 'f':
+						case 'n':
+						case 'p':
+						case 't':
+						case 'u':
+						case 'e':
+						case 'x':
+							success = listConstraints(&cmd[3], pattern, show_verbose, show_system);
+							break;
+						default:
+							status = PSQL_CMD_UNKNOWN;
+							break;
```
and
```
+	if (strlen(contypes) != strspn(contypes, dCN_options))
+	{
+		pg_log_error("\\dCN only takes [%s] as options", dCN_options);
+		return true;
+	}
```

These two checks are redundant. Is it intentional to keep both? It might be simpler to rely solely on listConstraints() for validation.

3
```
---- \dCN doesn't show constraints related to domain,
---- since \dD can be used to check them
```

I saw this in the test script, should we mention that in the doc change?

4
```
+ <term><literal>\dCN[cfnptue][Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>

```

Would it make sense to add a white space between [cfnptue] and [Sx+]? As you do so for the help message.

5
```
+	if (strlen(contypes) != strspn(contypes, dCN_options))
+	{
+		pg_log_error("\\dCN only takes [%s] as options", dCN_options);
+		return true;
+	}
+
+	if (pset.sversion < 180000)
+	{
+		char		sverbuf[32];
+
+		pg_log_error("The server (version %s) does not support this meta-command on psql.",
+					 formatPGVersionNumber(pset.sversion, false,
+										   sverbuf, sizeof(sverbuf)));
+		return true;
+	}
```

Why return true after pg_log_error?

6
```
+	if (!(showCheck || showForeign || showNotnull || showPrimary || showTrigger || showUnique || showExclusion))
+		showAllkinds = true;
```

Nit: this might be simplified as: showAllkinds = !(showCheck || …), then you don’t need to initialize showAllkinds as false.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Hi Chao-san,

Thanks for your comments!

On Thu, Jan 15, 2026 at 4:20 PM Chao Li <li.evan.chao@gmail.com> wrote:

Thanks for the patch. I just reviewed the patch, and I think it is solid

already,

just got a few small comments:

1 - commit message
```
\dCN shows all kind of constraints by using pg_constraint.
```

All kind -> all kinds

Oh, good catch!
I will fix it. Thanks!

2.
```
+                                       switch (cmd[3])
+                                       {
+                                               case '\0':
...
+                                                       success =

listConstraints(&cmd[3], pattern, show_verbose, show_system);

+                                                       break;
+                                               default:
+                                                       status =

PSQL_CMD_UNKNOWN;

+                                                       break;
```
and
```
+       if (strlen(contypes) != strspn(contypes, dCN_options))
+       {
+               pg_log_error("\\dCN only takes [%s] as options",

dCN_options);

+               return true;
+       }
```

These two checks are redundant. Is it intentional to keep both?
It might be simpler to rely solely on listConstraints() for validation.

The first check only verifies the next character following "CN".
For example, the following string would be passed to the listConstraints()
function:

\dCNcz

However, "z" is a non-existent option, so an error should be thrown.
This is why the second check is implemented: it reports an error if
the string contains anything other than the characters defined in
dCN_options.

This test case corresponds to the following regression test:
```
+---- Incorrect options will result in an error
+\dCNcz
+\dCN only takes [cfnptueSx+] as options
```

These two checks are also used in the existing \df, and \dCN follows
the same approach.

BTW, I just realized that this error message should be adjusted to align
with \? and the documentation. I will fix it later:
```
+\dCN only takes [cfnptue][Sx+] as options
```

3
```
---- \dCN doesn't show constraints related to domain,
---- since \dD can be used to check them
```

I saw this in the test script, should we mention that in the doc change?

Tom also commented on constraints related to domains, and I would like to
discuss and decide whether to include them in the \dCN command.

Depending on the decision, I am considering the following changes:

- If they are included:
- Remove the above test case from the regression tests
- If they are not included:
- Add the reason for excluding them to the documentation

Which do you think is better? Should domain constraints be covered by \dCN?
I would appreciate your feedback.

4
```
+ <term><literal>\dCN[cfnptue][Sx+] [ <link

linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>

```

Would it make sense to add a white space between [cfnptue] and [Sx+]? As

you do so for the help message.

Thanks for pointing that out.
I realized that this is an error in the help message, not the documentation.

$ psql -c '\?'|grep dCN
\dCN[cfnptue] [Sx+] [PATTERN]

I believe this syntax means that a space is required between the add-on
filter
[cfnptue] and [Sx+]. However, adding a space does not result in the
intended
behavior. Therefore, I will remove the space from the help message.

For example,
If there is a space before S, the pattern string will not be recognized.
# \dCNn S pg_const*
List of constraints
Schema | Name
--------+------
(0 rows)

If there is no space, the pattern string will be recognized.
# \dCNnS pg_const*
List of constraints
Schema | Name
------------+----------------------------
pg_catalog | pg_constraint_oid_not_null
(1 row)

5
```
...
+ pg_log_error("\\dCN only takes [%s] as options",

dCN_options);

+               return true;
...
+               pg_log_error("The server (version %s) does not support

this meta-command on psql.",

+

formatPGVersionNumber(pset.sversion, false,

+

sverbuf, sizeof(sverbuf)));

+ return true;
```

Why return true after pg_log_error?

I believe these functions return true because this is not considered
a fatal error for the psql command itself.
The only case where they seem to return false is when NULL is returned
after issuing a query with PSQLexec().

This behavior is consistent with other commands such as \df, so I do
not think there is a particular problem with keeping it as is.

6
```
+ if (!(showCheck || showForeign || showNotnull || showPrimary ||

showTrigger || showUnique || showExclusion))

+ showAllkinds = true;
```

Nit: this might be simplified as: showAllkinds = !(showCheck || …), then

you don’t need to initialize showAllkinds as false.

Personally, I do not think there is any major issue with the current code,
but since you suggested it, I will simplify it.

The next patch will include the following changes based on your comments:
- 1. Fix the commit message
- s/all kind/all kinds/
- 2. Fix the error message
- s/dCN only takes [cfnptueSx+]/dCN only takes [cfnptue][Sx+]/
- 4. Fix the help message
- s/\dCN[cfnptue] [Sx+] [PATTERN]/\dCN[cfnptue][Sx+] [PATTERN]/
- 6. Simplify the code that sets the value of showAllkinds

Thanks,
Tatsuro Yamada

On Jan 15, 2026, at 17:20, Tatsuro Yamada <yamatattsu@gmail.com> wrote:

Hi Chao-san,

Thanks for your comments!

On Thu, Jan 15, 2026 at 4:20 PM Chao Li <li.evan.chao@gmail.com> wrote:

3
```
---- \dCN doesn't show constraints related to domain,
---- since \dD can be used to check them
```

I saw this in the test script, should we mention that in the doc change?

Tom also commented on constraints related to domains, and I would like to
discuss and decide whether to include them in the \dCN command.

Depending on the decision, I am considering the following changes:

- If they are included:
- Remove the above test case from the regression tests
- If they are not included:
- Add the reason for excluding them to the documentation

Which do you think is better? Should domain constraints be covered by \dCN?
I would appreciate your feedback.

I had a feeling while reviewing the patch but I didn’t raise it because I was not firm minded. As you ask, this is just my personal opinion, you may ignore if you don’t consider reasonable.

This patch claims “constraints”, but it actually only shows table constraints. How, we can see the code comment says "Describes constraints”, the command message says "List of constraints”, I think that’s where the discussion came from.

Maybe an easy way to go is something like renaming the command to \dTCN, making it specific to table constraints.

6
```
+       if (!(showCheck || showForeign || showNotnull || showPrimary || showTrigger || showUnique || showExclusion))
+               showAllkinds = true;
```

Nit: this might be simplified as: showAllkinds = !(showCheck || …), then you don’t need to initialize showAllkinds as false.

Personally, I do not think there is any major issue with the current code,
but since you suggested it, I will simplify it.

Yeah, I marked it as “nit”.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Jan 15, 2026, at 17:53, Chao Li <li.evan.chao@gmail.com> wrote:

On Jan 15, 2026, at 17:20, Tatsuro Yamada <yamatattsu@gmail.com> wrote:

Hi Chao-san,

Thanks for your comments!

On Thu, Jan 15, 2026 at 4:20 PM Chao Li <li.evan.chao@gmail.com> wrote:

3
```
---- \dCN doesn't show constraints related to domain,
---- since \dD can be used to check them
```

I saw this in the test script, should we mention that in the doc change?

Tom also commented on constraints related to domains, and I would like to
discuss and decide whether to include them in the \dCN command.

Depending on the decision, I am considering the following changes:

- If they are included:
- Remove the above test case from the regression tests
- If they are not included:
- Add the reason for excluding them to the documentation

Which do you think is better? Should domain constraints be covered by \dCN?
I would appreciate your feedback.

I had a feeling while reviewing the patch but I didn’t raise it because I was not firm minded. As you ask, this is just my personal opinion, you may ignore if you don’t consider reasonable.

This patch claims “constraints”, but it actually only shows table constraints. How, we can see the code comment says "Describes constraints”, the command message says "List of constraints”, I think that’s where the discussion came from.

Maybe an easy way to go is something like renaming the command to \dTCN, making it specific to table constraints.

I just want to add that, I am not sure if “table constraints” is a proper group, and I didn’t intend to suggest a new command name as \dTCN. My point was to make the feature scope more specific, and make the command name better reflect to the scope.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Hi Jim, Tom, and Chao,

On Thu, Jan 15, 2026 at 6:20 PM Tatsuro Yamada <yamatattsu@gmail.com> wrote:

The next patch will include the following changes based on your comments:
- 1. Fix the commit message
- s/all kind/all kinds/
- 2. Fix the error message
- s/dCN only takes [cfnptueSx+]/dCN only takes [cfnptue][Sx+]/
- 4. Fix the help message
- s/\dCN[cfnptue] [Sx+] [PATTERN]/\dCN[cfnptue][Sx+] [PATTERN]/
- 6. Simplify the code that sets the value of showAllkinds

The attached patch addresses Chao's comments.
Please find the attached file.

Regards,
Tatsuro Yamada

Hi Chao, Jim, and Tom,

On Thu, Jan 15, 2026 at 7:49 PM Chao Li <li.evan.chao@gmail.com> wrote:

Which do you think is better? Should domain constraints be covered by

\dCN?

I would appreciate your feedback.

I had a feeling while reviewing the patch but I didn’t raise it
because I was not firm minded. As you ask, this is just my personal
opinion, you may ignore if you don’t consider reasonable.

This patch claims “constraints”, but it actually only shows table
constraints. How, we can see the code comment says "Describes

constraints”,

the command message says "List of constraints”, I think that’s where
the discussion came from.

Maybe an easy way to go is something like renaming the command to \dTCN,
making it specific to table constraints.

I just want to add that, I am not sure if “table constraints” is a
proper group, and I didn’t intend to suggest a new command name as \dTCN.
My point was to make the feature scope more specific, and make the command
name better reflect to the scope.

Thank you for your feedback.

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.
```
SELECT n.nspname AS "Schema",
cns.conname AS "Name" ,
pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition",
c.relname AS "Table"
FROM pg_catalog.pg_constraint cns
JOIN pg_catalog.pg_namespace n ON n.oid = cns.connamespace
LEFT JOIN pg_catalog.pg_type t ON t.oid = cns.contypid
LEFT JOIN pg_catalog.pg_class c on c.oid = cns.conrelid
WHERE n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND (
(cns.conrelid <> 0 AND pg_catalog.pg_table_is_visible(cns.conrelid))
OR (cns.contypid > 0 AND pg_catalog.pg_type_is_visible(t.oid))
)
ORDER BY 1, 2, 4;
```

For reference, here is the old query from the v4 patch:
```
SELECT n.nspname AS "Schema",
cst.conname AS "Name" ,
pg_catalog.pg_get_constraintdef(cst.oid) AS "Definition",
c.relname AS "Table"
FROM pg_catalog.pg_constraint cst
JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2, 4;
```

Please find attached patch.
Comments and suggestions are welcome.

Regards,
Tatsuro Yamada

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)

Hi Alvaro,

Thank you for your comments.

On Sat, Jan 17, 2026 at 1:01 AM Álvaro Herrera <alvherre@kurilemu.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

On 2026-Jan-19, Tatsuro Yamada wrote:

Hello,

# 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

Yeah, that sounds good to me.

Thanks!

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)