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

[Jim Jones]

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