Hi,
On Sat, Jan 10, 2026 at 12:14 PM Tatsuro Yamada <[email protected]>
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
From e56619698712ee3395d27b47ffc5f421e27c91a2 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <[email protected]>
Date: Mon, 12 Jan 2026 14:26:20 +0900
Subject: [PATCH] Add list constraints meta-command \dcs on psql
\dcs shows all kind of constraints by using pg_constraint.
You can filter constraints by appending c/f/n/p/t/u/e to \dcs.
For example, \dcsc will show only check constraints.
This patch also includes:
- document
- regression test
- tab completion
Changes since the last patch:
- Rebased version
- Cosmetic changes
- Add incorrect option check
- Use validateSQLNamePattern() instead of processSQLNamePattern()
- Fix bug \dcs with x (expanded display) option
- Use c.relname instead cst.conrelid::pg_catalog.regclass AS "Table"
on sql
- Add table name into ORDER BY clause 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
---
doc/src/sgml/ref/psql-ref.sgml | 20 +-
src/bin/psql/command.c | 20 ++
src/bin/psql/describe.c | 132 +++++++++
src/bin/psql/describe.h | 4 +
src/bin/psql/help.c | 2 +
src/bin/psql/tab-complete.in.c | 4 +-
src/test/regress/expected/psql.out | 451 +++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 110 +++++++
8 files changed, 741 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f56c70263e0..3a6c56d7adc 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1548,6 +1548,25 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-dcs">
+ <term><literal>\dcs[cfnptue][Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists constraints.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only entries whose name matches the pattern are listed.
+ The modifiers <literal>c</literal> (check), <literal>f</literal> (foreign key),
+ <literal>n</literal> (not-null), <literal>p</literal> (primary key),
+ <literal>t</literal> (trigger), <literal>u</literal> (unique),
+ <literal>e</literal> (exclusion) can be appended to the command,
+ filtering the kind of constraints to list.
+ By default, only user-created constraints are shown; supply the
+ <literal>S</literal> modifier to include system objects.
+ If <literal>+</literal> is appended to the command name, each object
+ is listed with its associated description.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry id="app-psql-meta-command-dc-uc">
<term><literal>\dC[x+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1566,7 +1585,6 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
-
<varlistentry id="app-psql-meta-command-dd-lc">
<term><literal>\dd[Sx] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 213d48500de..7fb6bde8746 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1103,6 +1103,26 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = describeConfigurationParameters(pattern,
show_verbose,
show_system);
+ else if (strncmp(cmd, "dcs", 3) == 0) /* Constraint */
+ 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;
+ }
else
success = listConversions(pattern,
show_verbose,
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3584c4e1428..0fff3207708 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4980,6 +4980,138 @@ listExtendedStats(const char *pattern)
return true;
}
+/*
+ * \dcs
+ * Describes constraints
+ *
+ * As with \d, you can specify the kinds of constraints you want:
+ *
+ * c for check
+ * f for foreign key
+ * n for not null
+ * p for primary key
+ * t for trigger
+ * u for unique
+ * e for exclusion
+ *
+ * and you can mix and match these in any order.
+ */
+bool
+listConstraints(const char *contypes, const char *pattern, bool verbose, bool showSystem)
+{
+ const char *dcs_options = "cfnptueSx+";
+ bool showCheck = strchr(contypes, CONSTRAINT_CHECK) != NULL;
+ bool showForeign = strchr(contypes, CONSTRAINT_FOREIGN) != NULL;
+ bool showNotnull = strchr(contypes, CONSTRAINT_NOTNULL) != NULL;
+ bool showPrimary = strchr(contypes, CONSTRAINT_PRIMARY) != NULL;
+ bool showTrigger = strchr(contypes, CONSTRAINT_TRIGGER) != NULL;
+ bool showUnique = strchr(contypes, CONSTRAINT_UNIQUE) != NULL;
+ /* 'x' is already used for expanded display, so use 'e' instead */
+ bool showExclusion = strchr(contypes, 'e') != NULL;
+ bool showAllkinds = false;
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (strlen(contypes) != strspn(contypes, dcs_options))
+ {
+ pg_log_error("\\dcs only takes [%s] as options", dcs_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;
+ }
+
+ /* If contypes were not selected, show them all */
+ if (!(showCheck || showForeign || showNotnull || showPrimary || showTrigger || showUnique || showExclusion))
+ showAllkinds = true;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname AS \"%s\", \n"
+ " cst.conname AS \"%s\" ",
+ gettext_noop("Schema"),
+ gettext_noop("Name")
+ );
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.pg_get_constraintdef(cst.oid) AS \"%s\", \n"
+ " c.relname AS \"%s\" ",
+ gettext_noop("Definition"),
+ gettext_noop("Table")
+ );
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_constraint cst \n"
+ " JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace \n"
+ " JOIN pg_catalog.pg_class c on c.oid = cst.conrelid \n"
+ );
+
+ 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 (");
+
+ if (showCheck)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_CHECK) ",");
+ if (showForeign)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_FOREIGN) ",");
+ if (showNotnull)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_NOTNULL) ",");
+ if (showPrimary)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_PRIMARY) ",");
+ if (showTrigger)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_TRIGGER) ",");
+ if (showUnique)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_UNIQUE) ",");
+ if (showExclusion)
+ appendPQExpBufferStr(&buf, CppAsString2(CONSTRAINT_EXCLUSION) ",");
+
+ appendPQExpBufferStr(&buf, " ''"); /* dummy */
+ appendPQExpBufferStr(&buf, ")\n");
+ }
+
+ if (verbose)
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.title = _("List of constraints");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/*
* \dC
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index b60a2ad0e14..7b92b1c1ad0 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -83,6 +83,10 @@ extern bool listConversions(const char *pattern, bool verbose, bool showSystem);
extern bool describeConfigurationParameters(const char *pattern, bool verbose,
bool showSystem);
+/* \dcs */
+extern bool listConstraints(const char *contypes, const char *pattern, bool verbose,
+ bool showSystem);
+
/* \dC */
extern bool listCasts(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index dfd9dd73078..77dc1399149 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -230,6 +230,8 @@ slashUsage(unsigned short int pager)
HELP0(" \\db[x+] [PATTERN] list tablespaces\n");
HELP0(" \\dc[Sx+] [PATTERN] list conversions\n");
HELP0(" \\dconfig[x+] [PATTERN] list configuration parameters\n");
+ HELP0(" \\dcs[cfnptue] [Sx+] [PATTERN] list [only check/foreign key/not-null/primary key\n"
+ " /constraint trigger/unique key/exclusion] constraints\n");
HELP0(" \\dC[x+] [PATTERN] list casts\n");
HELP0(" \\dd[Sx] [PATTERN] show object descriptions not displayed elsewhere\n");
HELP0(" \\dD[Sx+] [PATTERN] list domains\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8b91bc00062..11e349ce0cf 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1925,7 +1925,7 @@ psql_completion(const char *text, int start, int end)
"\\connect", "\\conninfo", "\\C", "\\cd", "\\close_prepared", "\\copy",
"\\copyright", "\\crosstabview",
"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
- "\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
+ "\\db", "\\dc", "\\dconfig", "\\dcs", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
@@ -5474,6 +5474,8 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
else if (TailMatchesCS("\\dconfig*"))
COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_show_vars);
+ else if (TailMatchesCS("\\dcs*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_constraints_with_schema);
else if (TailMatchesCS("\\dD*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains);
else if (TailMatchesCS("\\des*"))
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c8f3932edf0..334053f4066 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5388,6 +5388,433 @@ List of configuration parameters
(1 row)
reset work_mem;
+-- check \dcs
+CREATE TABLE con_c (
+ primary_col SERIAL PRIMARY KEY
+);
+CREATE TABLE con_p (
+ primary_col SERIAL PRIMARY KEY,
+ notnull_col TEXT NOT NULL,
+ check_col INT CHECK (check_col >= 0),
+ foreign_col INT REFERENCES con_c(primary_col),
+ unique_col TEXT UNIQUE,
+ exclusion_col INT,
+ CONSTRAINT con_p_exclusion EXCLUDE USING btree (exclusion_col WITH =)
+);
+CREATE OR REPLACE FUNCTION trigger_hoge() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END;
+ $$ LANGUAGE PLPGSQL;
+CREATE CONSTRAINT TRIGGER con_p_trigger AFTER INSERT ON con_p
+ FOR EACH ROW EXECUTE PROCEDURE trigger_hoge();
+---- \dcs shows constraints
+\dcs con_*
+ List of constraints
+ Schema | Name
+--------+----------------------------
+ public | con_c_pkey
+ public | con_c_primary_col_not_null
+ public | con_p_check_col_check
+ public | con_p_exclusion
+ public | con_p_foreign_col_fkey
+ public | con_p_notnull_col_not_null
+ public | con_p_pkey
+ public | con_p_primary_col_not_null
+ public | con_p_trigger
+ public | con_p_unique_col_key
+(10 rows)
+
+\dcscfnptue con_*
+ List of constraints
+ Schema | Name
+--------+----------------------------
+ public | con_c_pkey
+ public | con_c_primary_col_not_null
+ public | con_p_check_col_check
+ public | con_p_exclusion
+ public | con_p_foreign_col_fkey
+ public | con_p_notnull_col_not_null
+ public | con_p_pkey
+ public | con_p_primary_col_not_null
+ public | con_p_trigger
+ public | con_p_unique_col_key
+(10 rows)
+
+\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)
+
+\dcscfnptue+ 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)
+
+\dcsc con_*
+ List of constraints
+ Schema | Name
+--------+-----------------------
+ public | con_p_check_col_check
+(1 row)
+
+\dcsc+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+-----------------------+--------------------------+-------
+ public | con_p_check_col_check | CHECK ((check_col >= 0)) | con_p
+(1 row)
+
+\dcsf con_*
+ List of constraints
+ Schema | Name
+--------+------------------------
+ public | con_p_foreign_col_fkey
+(1 row)
+
+\dcsf+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+------------------------+---------------------------------------------------------+-------
+ public | con_p_foreign_col_fkey | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col) | con_p
+(1 row)
+
+\dcsn con_*
+ List of constraints
+ Schema | Name
+--------+----------------------------
+ public | con_c_primary_col_not_null
+ public | con_p_notnull_col_not_null
+ public | con_p_primary_col_not_null
+(3 rows)
+
+\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)
+
+\dcsp con_*
+ List of constraints
+ Schema | Name
+--------+------------
+ public | con_c_pkey
+ public | con_p_pkey
+(2 rows)
+
+\dcsp+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+------------+---------------------------+-------
+ public | con_c_pkey | PRIMARY KEY (primary_col) | con_c
+ public | con_p_pkey | PRIMARY KEY (primary_col) | con_p
+(2 rows)
+
+\dcst con_*
+ List of constraints
+ Schema | Name
+--------+---------------
+ public | con_p_trigger
+(1 row)
+
+\dcst+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+---------------+------------+-------
+ public | con_p_trigger | TRIGGER | con_p
+(1 row)
+
+\dcsu con_*
+ List of constraints
+ Schema | Name
+--------+----------------------
+ public | con_p_unique_col_key
+(1 row)
+
+\dcsu+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+----------------------+---------------------+-------
+ public | con_p_unique_col_key | UNIQUE (unique_col) | con_p
+(1 row)
+
+\dcse con_*
+ List of constraints
+ Schema | Name
+--------+-----------------
+ public | con_p_exclusion
+(1 row)
+
+\dcse+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+-----------------+--------------------------------------------+-------
+ public | con_p_exclusion | EXCLUDE USING btree (exclusion_col WITH =) | con_p
+(1 row)
+
+\dcsx con_*
+List of constraints
+-[ RECORD 1 ]----------------------
+Schema | public
+Name | con_c_pkey
+-[ RECORD 2 ]----------------------
+Schema | public
+Name | con_c_primary_col_not_null
+-[ RECORD 3 ]----------------------
+Schema | public
+Name | con_p_check_col_check
+-[ RECORD 4 ]----------------------
+Schema | public
+Name | con_p_exclusion
+-[ RECORD 5 ]----------------------
+Schema | public
+Name | con_p_foreign_col_fkey
+-[ RECORD 6 ]----------------------
+Schema | public
+Name | con_p_notnull_col_not_null
+-[ RECORD 7 ]----------------------
+Schema | public
+Name | con_p_pkey
+-[ RECORD 8 ]----------------------
+Schema | public
+Name | con_p_primary_col_not_null
+-[ RECORD 9 ]----------------------
+Schema | public
+Name | con_p_trigger
+-[ RECORD 10 ]---------------------
+Schema | public
+Name | con_p_unique_col_key
+
+\dcsx+ con_*
+List of constraints
+-[ RECORD 1 ]-------------------------------------------------------
+Schema | public
+Name | con_c_pkey
+Definition | PRIMARY KEY (primary_col)
+Table | con_c
+-[ RECORD 2 ]-------------------------------------------------------
+Schema | public
+Name | con_c_primary_col_not_null
+Definition | NOT NULL primary_col
+Table | con_c
+-[ RECORD 3 ]-------------------------------------------------------
+Schema | public
+Name | con_p_check_col_check
+Definition | CHECK ((check_col >= 0))
+Table | con_p
+-[ RECORD 4 ]-------------------------------------------------------
+Schema | public
+Name | con_p_exclusion
+Definition | EXCLUDE USING btree (exclusion_col WITH =)
+Table | con_p
+-[ RECORD 5 ]-------------------------------------------------------
+Schema | public
+Name | con_p_foreign_col_fkey
+Definition | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col)
+Table | con_p
+-[ RECORD 6 ]-------------------------------------------------------
+Schema | public
+Name | con_p_notnull_col_not_null
+Definition | NOT NULL notnull_col
+Table | con_p
+-[ RECORD 7 ]-------------------------------------------------------
+Schema | public
+Name | con_p_pkey
+Definition | PRIMARY KEY (primary_col)
+Table | con_p
+-[ RECORD 8 ]-------------------------------------------------------
+Schema | public
+Name | con_p_primary_col_not_null
+Definition | NOT NULL primary_col
+Table | con_p
+-[ RECORD 9 ]-------------------------------------------------------
+Schema | public
+Name | con_p_trigger
+Definition | TRIGGER
+Table | con_p
+-[ RECORD 10 ]------------------------------------------------------
+Schema | public
+Name | con_p_unique_col_key
+Definition | UNIQUE (unique_col)
+Table | con_p
+
+\dcsS pg_constraint*
+ List of constraints
+ Schema | Name
+------------+-----------------------------------------------
+ pg_catalog | pg_constraint_conrelid_contypid_conname_index
+ pg_catalog | pg_constraint_oid_index
+ pg_catalog | pg_constraint_oid_not_null
+(3 rows)
+
+\dcsS+ pg_constraint*
+ List of constraints
+ Schema | Name | Definition | Table
+------------+-----------------------------------------------+--------------------------------------+---------------
+ pg_catalog | pg_constraint_conrelid_contypid_conname_index | UNIQUE (conrelid, contypid, conname) | pg_constraint
+ pg_catalog | pg_constraint_oid_index | PRIMARY KEY (oid) | pg_constraint
+ pg_catalog | pg_constraint_oid_not_null | NOT NULL oid | pg_constraint
+(3 rows)
+
+\dcscfnpueS pg_constraint*
+ List of constraints
+ Schema | Name
+------------+-----------------------------------------------
+ pg_catalog | pg_constraint_conrelid_contypid_conname_index
+ pg_catalog | pg_constraint_oid_index
+ pg_catalog | pg_constraint_oid_not_null
+(3 rows)
+
+\dcscfnpueS+ pg_constraint*
+ List of constraints
+ Schema | Name | Definition | Table
+------------+-----------------------------------------------+--------------------------------------+---------------
+ pg_catalog | pg_constraint_conrelid_contypid_conname_index | UNIQUE (conrelid, contypid, conname) | pg_constraint
+ pg_catalog | pg_constraint_oid_index | PRIMARY KEY (oid) | pg_constraint
+ pg_catalog | pg_constraint_oid_not_null | NOT NULL oid | pg_constraint
+(3 rows)
+
+\dcscfnpueS+x pg_constraint*
+List of constraints
+-[ RECORD 1 ]---------------------------------------------
+Schema | pg_catalog
+Name | pg_constraint_conrelid_contypid_conname_index
+Definition | UNIQUE (conrelid, contypid, conname)
+Table | pg_constraint
+-[ RECORD 2 ]---------------------------------------------
+Schema | pg_catalog
+Name | pg_constraint_oid_index
+Definition | PRIMARY KEY (oid)
+Table | pg_constraint
+-[ RECORD 3 ]---------------------------------------------
+Schema | pg_catalog
+Name | pg_constraint_oid_not_null
+Definition | NOT NULL oid
+Table | pg_constraint
+
+---- \dcs doesn't show constraints related to domain,
+---- since \dD can be used to check them
+CREATE DOMAIN notnulldomain integer NOT NULL;
+CREATE TABLE table_used_domain (
+ col notnulldomain
+);
+\dcs+ notnulldomain*;
+ List of constraints
+ Schema | Name | Definition | Table
+--------+------+------------+-------
+(0 rows)
+
+---- Incorrect options will result in an error
+\dcscz
+\dcs only takes [cfnptueSx+] as options
+---- test with search_path
+CREATE SCHEMA con_schema;
+CREATE TABLE con_schema.con_schema_test (
+ primary_col SERIAL PRIMARY KEY
+);
+SET SEARCH_PATH TO public, con_schema;
+\dcs con_*
+ List of constraints
+ Schema | Name
+------------+--------------------------------------
+ con_schema | con_schema_test_pkey
+ con_schema | con_schema_test_primary_col_not_null
+ public | con_c_pkey
+ public | con_c_primary_col_not_null
+ public | con_p_check_col_check
+ public | con_p_exclusion
+ public | con_p_foreign_col_fkey
+ public | con_p_notnull_col_not_null
+ public | con_p_pkey
+ public | con_p_primary_col_not_null
+ public | con_p_trigger
+ public | con_p_unique_col_key
+(12 rows)
+
+SET SEARCH_PATH TO public;
+\dcs con_*
+ List of constraints
+ Schema | Name
+--------+----------------------------
+ public | con_c_pkey
+ public | con_c_primary_col_not_null
+ public | con_p_check_col_check
+ public | con_p_exclusion
+ public | con_p_foreign_col_fkey
+ public | con_p_notnull_col_not_null
+ public | con_p_pkey
+ public | con_p_primary_col_not_null
+ public | con_p_trigger
+ public | con_p_unique_col_key
+(10 rows)
+
+RESET search_path;
+---- test with inherits
+CREATE TABLE zoo_parent (
+ cage int PRIMARY KEY
+);
+CREATE TABLE zoo_child (
+ animal text
+) INHERITS (zoo_parent);
+\dcs+ zoo_parent_cage_not_null
+ List of constraints
+ Schema | Name | Definition | Table
+--------+--------------------------+---------------+------------
+ public | zoo_parent_cage_not_null | NOT NULL cage | zoo_child
+ public | zoo_parent_cage_not_null | NOT NULL cage | zoo_parent
+(2 rows)
+
+---- test with partitioned table
+CREATE TABLE zoo_part (
+ cage int,
+ animal text,
+ CONSTRAINT zoo_part_pk PRIMARY KEY (cage)
+) PARTITION BY RANGE (cage);
+CREATE TABLE zoo_part_1
+ PARTITION OF zoo_part
+ FOR VALUES FROM (0) TO (100);
+\dcs+ zoo_part*
+ 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)
+
+-- clean up for \dcs test cases
+DROP SCHEMA con_schema CASCADE;
+NOTICE: drop cascades to table con_schema.con_schema_test
+DROP TABLE con_p, con_c, table_used_domain, zoo_parent, zoo_part CASCADE;
+NOTICE: drop cascades to table zoo_child
+DROP FUNCTION trigger_hoge;
+DROP DOMAIN notnulldomain;
-- check \df, \do with argument specifications
\df *sqrt
List of functions
@@ -6100,6 +6527,10 @@ improper qualified name (too many dotted names): host.regression.public.conversi
cross-database references are not implemented: (.public.conversion
\dc nonesuch.public.conversion
cross-database references are not implemented: nonesuch.public.conversion
+\dcs host.regression.public.constraint
+improper qualified name (too many dotted names): host.regression.public.constraint
+\dcs nonesuch.public.constraint
+cross-database references are not implemented: nonesuch.public.constraint
\dC host.regression.pg_catalog.int8
improper qualified name (too many dotted names): host.regression.pg_catalog.int8
\dC ).pg_catalog.int8
@@ -6333,6 +6764,12 @@ List of access methods
--------+------+--------+-------------+----------
(0 rows)
+\dcs "no.such.constraint"
+List of constraints
+ Schema | Name
+--------+------
+(0 rows)
+
\dC "no.such.cast"
List of casts
Source type | Target type | Function | Implicit?
@@ -6543,6 +6980,12 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.table
--------+------+--------+-------------+----------
(0 rows)
+\dcs "no.such.schema"."no.such.constraint"
+List of constraints
+ Schema | Name
+--------+------
+(0 rows)
+
\dC "no.such.schema"."no.such.cast"
List of casts
Source type | Target type | Function | Implicit?
@@ -6700,6 +7143,12 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.event
--------+------+--------+-------------+----------
(0 rows)
+\dcs regression."no.such.schema"."no.such.constraint"
+List of constraints
+ Schema | Name
+--------+------
+(0 rows)
+
\dC regression."no.such.schema"."no.such.cast"
List of casts
Source type | Target type | Function | Implicit?
@@ -6821,6 +7270,8 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.aggregate.function"
\dc "no.such.database"."no.such.schema"."no.such.conversion"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.conversion"
+\dcs "no.such.database"."no.such.schema"."no.such.constraint"
+cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.constraint"
\dC "no.such.database"."no.such.schema"."no.such.cast"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.cast"
\dd "no.such.database"."no.such.schema"."no.such.object.description"
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index dcdbd4fc020..714e59573c2 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1347,6 +1347,110 @@ set work_mem = 10240;
\dconfig+ work*
reset work_mem;
+-- check \dcs
+CREATE TABLE con_c (
+ primary_col SERIAL PRIMARY KEY
+);
+
+CREATE TABLE con_p (
+ primary_col SERIAL PRIMARY KEY,
+ notnull_col TEXT NOT NULL,
+ check_col INT CHECK (check_col >= 0),
+ foreign_col INT REFERENCES con_c(primary_col),
+ unique_col TEXT UNIQUE,
+ exclusion_col INT,
+ CONSTRAINT con_p_exclusion EXCLUDE USING btree (exclusion_col WITH =)
+);
+
+CREATE OR REPLACE FUNCTION trigger_hoge() RETURNS TRIGGER AS $$
+ BEGIN
+ RETURN NULL;
+ END;
+ $$ LANGUAGE PLPGSQL;
+
+CREATE CONSTRAINT TRIGGER con_p_trigger AFTER INSERT ON con_p
+ FOR EACH ROW EXECUTE PROCEDURE trigger_hoge();
+
+---- \dcs shows constraints
+\dcs con_*
+\dcscfnptue con_*
+\dcs+ con_*
+\dcscfnptue+ con_*
+\dcsc con_*
+\dcsc+ con_*
+\dcsf con_*
+\dcsf+ con_*
+\dcsn con_*
+\dcsn+ con_*
+\dcsp con_*
+\dcsp+ con_*
+\dcst con_*
+\dcst+ con_*
+\dcsu con_*
+\dcsu+ con_*
+\dcse con_*
+\dcse+ con_*
+\dcsx con_*
+\dcsx+ con_*
+\dcsS pg_constraint*
+\dcsS+ pg_constraint*
+\dcscfnpueS pg_constraint*
+\dcscfnpueS+ pg_constraint*
+\dcscfnpueS+x pg_constraint*
+
+---- \dcs doesn't show constraints related to domain,
+---- since \dD can be used to check them
+CREATE DOMAIN notnulldomain integer NOT NULL;
+CREATE TABLE table_used_domain (
+ col notnulldomain
+);
+\dcs+ notnulldomain*;
+
+---- Incorrect options will result in an error
+\dcscz
+
+---- test with search_path
+CREATE SCHEMA con_schema;
+CREATE TABLE con_schema.con_schema_test (
+ primary_col SERIAL PRIMARY KEY
+);
+
+SET SEARCH_PATH TO public, con_schema;
+\dcs con_*
+SET SEARCH_PATH TO public;
+\dcs con_*
+RESET search_path;
+
+---- test with inherits
+CREATE TABLE zoo_parent (
+ cage int PRIMARY KEY
+);
+
+CREATE TABLE zoo_child (
+ animal text
+) INHERITS (zoo_parent);
+
+\dcs+ zoo_parent_cage_not_null
+
+---- test with partitioned table
+CREATE TABLE zoo_part (
+ cage int,
+ animal text,
+ CONSTRAINT zoo_part_pk PRIMARY KEY (cage)
+) PARTITION BY RANGE (cage);
+
+CREATE TABLE zoo_part_1
+ PARTITION OF zoo_part
+ FOR VALUES FROM (0) TO (100);
+
+\dcs+ zoo_part*
+
+-- clean up for \dcs test cases
+DROP SCHEMA con_schema CASCADE;
+DROP TABLE con_p, con_c, table_used_domain, zoo_parent, zoo_part CASCADE;
+DROP FUNCTION trigger_hoge;
+DROP DOMAIN notnulldomain;
+
-- check \df, \do with argument specifications
\df *sqrt
\df *sqrt num*
@@ -1683,6 +1787,8 @@ DROP FUNCTION psql_error;
\dc host.regression.public.conversion
\dc (.public.conversion
\dc nonesuch.public.conversion
+\dcs host.regression.public.constraint
+\dcs nonesuch.public.constraint
\dC host.regression.pg_catalog.int8
\dC ).pg_catalog.int8
\dC nonesuch.pg_catalog.int8
@@ -1783,6 +1889,7 @@ DROP FUNCTION psql_error;
\dAp "no.such.operator.support.function.of.operator.family"
\db "no.such.tablespace"
\dc "no.such.conversion"
+\dcs "no.such.constraint"
\dC "no.such.cast"
\dd "no.such.object.description"
\dD "no.such.domain"
@@ -1824,6 +1931,7 @@ DROP FUNCTION psql_error;
\dAp "no.such.schema"."no.such.operator.support.function.of.operator.family"
\db "no.such.schema"."no.such.tablespace"
\dc "no.such.schema"."no.such.conversion"
+\dcs "no.such.schema"."no.such.constraint"
\dC "no.such.schema"."no.such.cast"
\dd "no.such.schema"."no.such.object.description"
\dD "no.such.schema"."no.such.domain"
@@ -1858,6 +1966,7 @@ DROP FUNCTION psql_error;
\dt regression."no.such.schema"."no.such.table.relation"
\da regression."no.such.schema"."no.such.aggregate.function"
\dc regression."no.such.schema"."no.such.conversion"
+\dcs regression."no.such.schema"."no.such.constraint"
\dC regression."no.such.schema"."no.such.cast"
\dd regression."no.such.schema"."no.such.object.description"
\dD regression."no.such.schema"."no.such.domain"
@@ -1882,6 +1991,7 @@ DROP FUNCTION psql_error;
\dt "no.such.database"."no.such.schema"."no.such.table.relation"
\da "no.such.database"."no.such.schema"."no.such.aggregate.function"
\dc "no.such.database"."no.such.schema"."no.such.conversion"
+\dcs "no.such.database"."no.such.schema"."no.such.constraint"
\dC "no.such.database"."no.such.schema"."no.such.cast"
\dd "no.such.database"."no.such.schema"."no.such.object.description"
\dD "no.such.database"."no.such.schema"."no.such.domain"
--
2.43.5
