Thread
-
[PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2025-10-31T15:19:07Z
Hi hackers, Until PostgreSQL 17, if you wanted to inspect constraints in a database, you had to either: - use the "\d" command to check constraints per table, or - query "pg_constraint" and "pg_attribute" directly. However, starting from PG18, thanks to Álvaro's work [1], NOT NULL constraints are now included in "pg_constraint". This means that by querying "pg_constraint", we can now obtain all kinds of constraints [2][3] directly. Building on that improvement, I would like to propose a new psql meta-command to **list all constraints** in the database. ## Motivation This command would help DBAs and users to easily understand: - which tables have constraints - how many constraints exist in the database overall - whether all constraints have been properly created after a migration, etc. In other words, it would serve as a convenient tool for quickly validating schema integrity. Given that psql already has a wide variety of meta-commands — from frequently used to rarely used ones — I believe adding this one would not cause any issues. ## Usage examples -- Show all constraints \dcs+ con_* List of constraints Schema | Name | Definition | Table --------+----------------------------+---------------------- -----------------------------------+------- public | con_c_pkey | PRIMARY KEY (primary_col) | con_c public | con_c_primary_col_not_null | NOT NULL primary_col | con_c public | con_p_check_col_check | CHECK ((check_col >= 0)) | con_p public | con_p_exclusion | EXCLUDE USING btree (exclusion_col WITH =) | con_p public | con_p_foreign_col_fkey | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col) | con_p public | con_p_notnull_col_not_null | NOT NULL notnull_col | con_p public | con_p_pkey | PRIMARY KEY (primary_col) | con_p public | con_p_primary_col_not_null | NOT NULL primary_col | con_p public | con_p_trigger | TRIGGER | con_p public | con_p_unique_col_key | UNIQUE (unique_col) | con_p (10 rows) -- Show only NOT NULL constraints (added "n" for filter) \dcsn+ con_* List of constraints Schema | Name | Definition | Table --------+----------------------------+----------------------+------- public | con_c_primary_col_not_null | NOT NULL primary_col | con_c public | con_p_notnull_col_not_null | NOT NULL notnull_col | con_p public | con_p_primary_col_not_null | NOT NULL primary_col | con_p (3 rows) ## About the patch The patch includes: - \dcs meta-command - Tab completion support - Regression tests - Documentation and applies cleanly to the master branch. ## Discussion point: meta-command naming I'd like to ask for opinions (and votes) on the command name. Here are some candidates. The one with the most votes will be adopted as the final name. \dcs -> uses the first letters of the two syllables in "con-straint" \dco -> short form using the first two letters of "constraint" \G -> an idea based on the synonym "guarantee," which semantically fits the concept Please find the attached file. Feedback and suggestions are very welcome. [1]: Changes to NOT NULL in Postgres 18 https://www.enterprisedb.com/blog/changes-not-null-postgres-18 [2]: 5.5. Constraints https://www.postgresql.org/docs/current/ddl-constraints.html [3]: constraint trigger https://www.postgresql.org/docs/current/sql-createtrigger.html#id-1.9.3.93.6 Thanks, Tatsuro Yamada -
Re: [PATCH] psql: add \dcs to list all constraints
Jim Jones <jim.jones@uni-muenster.de> — 2026-01-02T19:51:12Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-10T03:14:33Z
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
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-12T05:39:21Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Jim Jones <jim.jones@uni-muenster.de> — 2026-01-12T15:17:31Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-15T01:48:43Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Tom Lane <tgl@sss.pgh.pa.us> — 2026-01-15T02:15:37Z
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
-
Re: [PATCH] psql: add \dcs to list all constraints
Tom Lane <tgl@sss.pgh.pa.us> — 2026-01-15T03:59:10Z
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
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-15T04:43:50Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-15T05:12:23Z
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 >
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-15T06:32:51Z
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. https://www.postgresql.org/message-id/CAOKkKFuYfdvpQ7eSYWxB1YrzwVafWm%2B%2BctXBPe_Rb1YqeOKjjA%40mail.gmail.com Regards, Tatsuro Yamada
-
Re: [PATCH] psql: add \dcs to list all constraints
Chao Li <li.evan.chao@gmail.com> — 2026-01-15T07:20:14Z
> 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. > > https://www.postgresql.org/message-id/CAOKkKFuYfdvpQ7eSYWxB1YrzwVafWm%2B%2BctXBPe_Rb1YqeOKjjA%40mail.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/ -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-15T09:20:52Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Chao Li <li.evan.chao@gmail.com> — 2026-01-15T09:53:10Z
> 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/
-
Re: [PATCH] psql: add \dcs to list all constraints
Chao Li <li.evan.chao@gmail.com> — 2026-01-15T10:49:15Z
> 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/
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-15T14:08:43Z
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
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-16T10:37:00Z
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 -
Re: [PATCH] psql: add \dcs to list all constraints
Álvaro Herrera <alvherre@kurilemu.de> — 2026-01-16T11:21:37Z
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) -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-19T09:22:18Z
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
-
Re: [PATCH] psql: add \dcs to list all constraints
Álvaro Herrera <alvherre@kurilemu.de> — 2026-01-19T13:09:48Z
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)
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-19T13:44:01Z
Hi Álvaro, and ALL, >I tested this with one million entries, and the query you proposed was >more than 1,000 times faster. Sorry, I just realized that I accidentally added an unnecessary filter condition to one of the queries. Specifically, the extra condition was: AND n.nspname LIKE 'many_cns' Because of this, I was not able to measure the two queries under identical conditions. I will rerun the benchmarks and share the updated results tomorrow. > # 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. > Hopefully, I will also be able to submit a new patch along with the revised measurement results tomorrow. Regards, Tatsuro Yamada -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-20T10:01:04Z
Hi Álvaro and ALL, On Mon, Jan 19, 2026 at 10:44 PM Tatsuro Yamada <yamatattsu@gmail.com> wrote: > Sorry, I just realized that I accidentally added an unnecessary filter > condition to one of the queries. > Specifically, the extra condition was: > AND n.nspname LIKE 'many_cns' > > Because of this, I was not able to measure the two queries under identical > conditions. I will rerun the benchmarks and share the updated results > tomorrow. > The error in the query has been fixed, and the execution times were measured again. As a result, there was no significant difference in execution time between the two queries (see the attached test_result2.txt). When comparing the cost of the top node, the original query has a slightly lower cost, so I decided to keep it as is. > # 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. >> > > Hopefully, I will also be able to submit a new patch along with the > revised > measurement results tomorrow. > The patch (v6) incorporates the changes listed above. Note that, as mentioned earlier, the query itself was not changed. Below is an example of the result set after the corrections (from the regression test): \dCN con_* List of constraints Schema | Table | Type | Name --------+-------+-----------+---------------------------- public | con_c | NOT NULL | con_c_primary_col_not_null public | con_c | PK | con_c_pkey public | con_p | CHECK | con_p_check_col_check public | con_p | EXCLUSION | con_p_exclusion public | con_p | FK | con_p_foreign_col_fkey public | con_p | NOT NULL | con_p_notnull_col_not_null public | con_p | NOT NULL | con_p_primary_col_not_null public | con_p | PK | con_p_pkey public | con_p | TRIGGER | con_p_trigger public | con_p | UNIQUE | con_p_unique_col_key (10 rows) What do you think? Please find the attached patch. Regards, Tatsuro Yamada -
Re: [PATCH] psql: add \dcs to list all constraints
Álvaro Herrera <alvherre@kurilemu.de> — 2026-01-20T12:30:22Z
On 2026-Jan-20, Tatsuro Yamada wrote: > The error in the query has been fixed, and the execution times were > measured again. > As a result, there was no significant difference in execution time > between the two queries (see the attached test_result2.txt). > When comparing the cost of the top node, the original query has a > slightly lower cost, so I decided to keep it as is. Yeah, these results match my own tests -- the query I proposed is a bit slower. Not by a lot, but measurably so. (I did ran my disk full by running pgbench with the two queries in separate files and the one million constraints; temp files for sort are quite large. My query is pretty consistently reported to be slower than the other anyway.) > List of constraints > Schema | Table | Type | Name > --------+-------+-----------+---------------------------- > public | con_c | NOT NULL | con_c_primary_col_not_null > public | con_c | PK | con_c_pkey > public | con_p | CHECK | con_p_check_col_check > public | con_p | EXCLUSION | con_p_exclusion > public | con_p | FK | con_p_foreign_col_fkey > public | con_p | NOT NULL | con_p_notnull_col_not_null > public | con_p | NOT NULL | con_p_primary_col_not_null > public | con_p | PK | con_p_pkey > public | con_p | TRIGGER | con_p_trigger > public | con_p | UNIQUE | con_p_unique_col_key > (10 rows) > > What do you think? Looks good. I think the constraint types ought to be lower-case and fully spelled out, with not-null having an hyphen as discussed elsewhere (though I couldn't find the reference), so not-null primary key exclusion check foreign key unique trigger I wonder if we should mark temporal constraints especially. (So "temporal primary key", etc). Without thinking too much about it, my impression is that it'd be useful. I didn't look at the patch closer, apologies. Thanks -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-21T04:40:31Z
Hi Álvaro, Thanks for your comments! On Tue, Jan 20, 2026 at 9:30 PM Álvaro Herrera <alvherre@kurilemu.de> wrote: > Yeah, these results match my own tests -- the query I proposed is a bit > slower. Not by a lot, but measurably so. (I did ran my disk full by > running pgbench with the two queries in separate files and the one > million constraints; temp files for sort are quite large. My query is > pretty consistently reported to be slower than the other anyway.) I see that you used pgbench to compare the throughput of the two queries. I’m glad that you obtained consistent results regardless of the individual environment. > > What do you think? > > Looks good. I think the constraint types ought to be lower-case and > fully spelled out, with not-null having an hyphen as discussed > elsewhere (though I couldn't find the reference), so > not-null > primary key > exclusion > check > foreign key > unique > trigger Thanks. I agree that all constraint types should be displayed in full and in lowercase. > I wonder if we should mark temporal constraints especially. (So > "temporal primary key", etc). Without thinking too much about it, my > impression is that it'd be useful. I was not aware of temporal constraints. Based on the article [1], I ran a quick test on temporal primary keys, and they appear to be displayed correctly as primary keys: -- Create employees table with temporal constraints CREATE TABLE employees ( emp_id INTEGER, emp_name VARCHAR(100) NOT NULL, department VARCHAR(50) NOT NULL, position VARCHAR(50) NOT NULL, salary DECIMAL(10,2) NOT NULL, valid_period tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'), -- Temporal primary key: no overlapping periods for same employee PRIMARY KEY (emp_id, valid_period WITHOUT OVERLAPS) ); # \dCNp+ List of constraints Schema | Table | Type | Name | Definition --------+----------+-------------+----------------+---------------------------------------------------- public | employees | primary key | employees_pkey | PRIMARY KEY (emp_id, valid_period WITHOUT OVERLAPS) (1 row) I will investigate other types of temporal constraints and consider adding them to the test cases. Thank you for pointing that out. The next patch will include the following two fixes: - Display all constraint types in full and in lowercase. - Add test cases for temporal constraints. [1] https://neon.com/postgresql/postgresql-18/temporal-constraints Regards, Tatsuro Yamada
-
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-21T08:46:45Z
Hi Álvaro and all, > The next patch will include the following two fixes: > - Display all constraint types in full and in lowercase. > - Add test cases for temporal constraints. The above two issues have been addressed in the V7 patch. Below is an example output. List of constraints Schema | Table | Type | Name --------+-----------------+-------------+------------------------------------ public | temporal_con_fk | foreign key | temporal_con_fk_p_id_valid_at_fkey public | temporal_con_pk | not-null | temporal_con_pk_id_not_null public | temporal_con_pk | not-null | temporal_con_pk_valid_at_not_null public | temporal_con_pk | primary key | temporal_con_pk_pkey public | temporal_con_uq | unique | temporal_con_uq_id_valid_at_key (5 rows) Please find the attached file. Regards, Tatsuro Yamada -
Re: [PATCH] psql: add \dcs to list all constraints
Tatsuro Yamada <yamatattsu@gmail.com> — 2026-01-22T13:03:52Z
Hi Chao, Álvaro, and all, >>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. After reviewing the code, I realized that the comment about passing everything to listconstraints() instead of just checking the third string was correct. Therefore, I updated the code accordingly. Thanks to Chao and Alvaro for their comments. I created the new patch (v8). Other fixes included: - Updated the documentation to clarify what the pattern string matches. - Modified the case statement to use a predefined variable when converting contypes to strings. Please find the attached file. Thanks, Tatsuro Yamada
-
Re: [PATCH] psql: add \dcs to list all constraints
Cary Huang <cary.huang@highgo.ca> — 2026-06-10T18:50:44Z
Hi The newly added \dCN meta-command in psql does return all constraints correctly, but it does not properly filter by table name. When a table name is provided as an argument, the command returns empty results instead of matching constraints on that table. For example: -- Setup CREATE TABLE con_test_table ( id INT PRIMARY KEY, name TEXT NOT NULL UNIQUE ); -- List all constraints (works correctly) \dCN -- List constraints on specific table (returns empty) \dCN con_test_table -- Match by constraint name (works correctly) \dCN con_test_table_pkey To fix, in "src/bin/psql/describe.c", in the `listConstraints()` function, change: ``` if (!validateSQLNamePattern(&buf, pattern, true, false, "n.nspname", "cns.conname", NULL, NULL, NULL, 3)) ``` To: ``` if (!validateSQLNamePattern(&buf, pattern, true, false, "n.nspname", "cns.conname", "c.relname", NULL, NULL, 3)) ``` to allow the pattern to match against table names as well. thanks Cary Huang ------------- HighGo Software (Canada) cary.huang@highgo.ca www.highgo.ca