v3-0001-Add-list-constraints-meta-command-dCN-on-psql.patch
application/octet-stream
Filename: v3-0001-Add-list-constraints-meta-command-dCN-on-psql.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v3-0001
Subject: Add list constraints meta-command \dCN on psql
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/psql-ref.sgml | 19 | 1 |
| src/bin/psql/command.c | 22 | 1 |
| src/bin/psql/describe.c | 135 | 0 |
| src/bin/psql/describe.h | 4 | 0 |
| src/bin/psql/help.c | 2 | 0 |
| src/bin/psql/tab-complete.in.c | 3 | 1 |
| src/test/regress/expected/psql.out | 451 | 0 |
| src/test/regress/sql/psql.sql | 110 | 0 |
From c5260f02c770f0ebfe9ed83c942f49f6de3d9d6c Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatattsu@gmail.com>
Date: Thu, 15 Jan 2026 14:51:24 +0900
Subject: [PATCH v3] Add list constraints meta-command \dCN on psql
\dCN shows all kind of constraints by using pg_constraint.
You can filter constraints by appending c/f/n/p/t/u/e to \dCN.
For example, \dCNc 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 \dCN 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
- Rename the command from \dcs to \dCN (proposed by Tom. Thanks!)
- Fix adding pg_class to the query string only if "+" is specified
(identified through Jim's additional testing. Thanks!)
---
doc/src/sgml/ref/psql-ref.sgml | 20 +-
src/bin/psql/command.c | 23 +-
src/bin/psql/describe.c | 135 +++++++++
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, 746 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f56c70263e0..751ea0bd256 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1548,7 +1548,6 @@ SELECT $1 \parse stmt1
</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>
<listitem>
@@ -1566,6 +1565,25 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-dCN">
+ <term><literal>\dCN[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-dd-lc">
<term><literal>\dd[Sx] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 213d48500de..cf5837a80d7 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1109,7 +1109,28 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
show_system);
break;
case 'C':
- success = listCasts(pattern, show_verbose);
+ if (strncmp(cmd, "dCN", 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 = listCasts(pattern, show_verbose);
break;
case 'd':
if (strncmp(cmd, "ddp", 3) == 0)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3584c4e1428..b850755642e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5104,6 +5104,141 @@ error_return:
return false;
}
+/*
+ * \dCN
+ * 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 *dCN_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, 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;
+ }
+
+ /* 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"
+ );
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " 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;
+}
+
/*
* \dO
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index b60a2ad0e14..94b5f27fc9e 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -86,6 +86,10 @@ extern bool describeConfigurationParameters(const char *pattern, bool verbose,
/* \dC */
extern bool listCasts(const char *pattern, bool verbose);
+/* \dCN */
+extern bool listConstraints(const char *contypes, const char *pattern, bool verbose,
+ bool showSystem);
+
/* \dO */
extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index dfd9dd73078..05dc3f635ba 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -231,6 +231,8 @@ slashUsage(unsigned short int pager)
HELP0(" \\dc[Sx+] [PATTERN] list conversions\n");
HELP0(" \\dconfig[x+] [PATTERN] list configuration parameters\n");
HELP0(" \\dC[x+] [PATTERN] list casts\n");
+ HELP0(" \\dCN[cfnptue] [Sx+] [PATTERN] list [only check/foreign key/not-null/primary key\n"
+ " /constraint trigger/unique key/exclusion] constraints\n");
HELP0(" \\dd[Sx] [PATTERN] show object descriptions not displayed elsewhere\n");
HELP0(" \\dD[Sx+] [PATTERN] list domains\n");
HELP0(" \\ddp[x] [PATTERN] list default privileges\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8b91bc00062..d8340132b78 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", "\\dC", "\\dCN", "\\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("\\dCN*"))
+ 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..d716d9fe7f5 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 \dCN
+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();
+---- \dCN shows constraints
+\dCN 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)
+
+\dCNcfnptue 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)
+
+\dCN+ 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)
+
+\dCNcfnptue+ 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)
+
+\dCNc con_*
+ List of constraints
+ Schema | Name
+--------+-----------------------
+ public | con_p_check_col_check
+(1 row)
+
+\dCNc+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+-----------------------+--------------------------+-------
+ public | con_p_check_col_check | CHECK ((check_col >= 0)) | con_p
+(1 row)
+
+\dCNf con_*
+ List of constraints
+ Schema | Name
+--------+------------------------
+ public | con_p_foreign_col_fkey
+(1 row)
+
+\dCNf+ 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)
+
+\dCNn 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)
+
+\dCNn+ 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)
+
+\dCNp con_*
+ List of constraints
+ Schema | Name
+--------+------------
+ public | con_c_pkey
+ public | con_p_pkey
+(2 rows)
+
+\dCNp+ 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)
+
+\dCNt con_*
+ List of constraints
+ Schema | Name
+--------+---------------
+ public | con_p_trigger
+(1 row)
+
+\dCNt+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+---------------+------------+-------
+ public | con_p_trigger | TRIGGER | con_p
+(1 row)
+
+\dCNu con_*
+ List of constraints
+ Schema | Name
+--------+----------------------
+ public | con_p_unique_col_key
+(1 row)
+
+\dCNu+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+----------------------+---------------------+-------
+ public | con_p_unique_col_key | UNIQUE (unique_col) | con_p
+(1 row)
+
+\dCNe con_*
+ List of constraints
+ Schema | Name
+--------+-----------------
+ public | con_p_exclusion
+(1 row)
+
+\dCNe+ con_*
+ List of constraints
+ Schema | Name | Definition | Table
+--------+-----------------+--------------------------------------------+-------
+ public | con_p_exclusion | EXCLUDE USING btree (exclusion_col WITH =) | con_p
+(1 row)
+
+\dCNx 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
+
+\dCNx+ 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
+
+\dCNS 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)
+
+\dCNS+ 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)
+
+\dCNcfnpueS 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)
+
+\dCNcfnpueS+ 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)
+
+\dCNcfnpueS+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
+
+---- \dCN 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
+);
+\dCN+ notnulldomain*;
+ List of constraints
+ Schema | Name | Definition | Table
+--------+------+------------+-------
+(0 rows)
+
+---- Incorrect options will result in an error
+\dCNcz
+\dCN 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;
+\dCN 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;
+\dCN 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);
+\dCN+ 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);
+\dCN+ 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 \dCN 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
@@ -6106,6 +6533,10 @@ improper qualified name (too many dotted names): host.regression.pg_catalog.int8
cross-database references are not implemented: ).pg_catalog.int8
\dC nonesuch.pg_catalog.int8
cross-database references are not implemented: nonesuch.pg_catalog.int8
+\dCN host.regression.public.constraint
+improper qualified name (too many dotted names): host.regression.public.constraint
+\dCN nonesuch.public.constraint
+cross-database references are not implemented: nonesuch.public.constraint
\dd host.regression.pg_catalog.pg_class
improper qualified name (too many dotted names): host.regression.pg_catalog.pg_class
\dd [.pg_catalog.pg_class
@@ -6339,6 +6770,12 @@ List of access methods
-------------+-------------+----------+-----------
(0 rows)
+\dCN "no.such.constraint"
+List of constraints
+ Schema | Name
+--------+------
+(0 rows)
+
\dd "no.such.object.description"
Object descriptions
Schema | Name | Object | Description
@@ -6549,6 +6986,12 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.table
-------------+-------------+----------+-----------
(0 rows)
+\dCN "no.such.schema"."no.such.constraint"
+List of constraints
+ Schema | Name
+--------+------
+(0 rows)
+
\dd "no.such.schema"."no.such.object.description"
Object descriptions
Schema | Name | Object | Description
@@ -6706,6 +7149,12 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.event
-------------+-------------+----------+-----------
(0 rows)
+\dCN regression."no.such.schema"."no.such.constraint"
+List of constraints
+ Schema | Name
+--------+------
+(0 rows)
+
\dd regression."no.such.schema"."no.such.object.description"
Object descriptions
Schema | Name | Object | Description
@@ -6823,6 +7272,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.conversion"
\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"
+\dCN "no.such.database"."no.such.schema"."no.such.constraint"
+cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.constraint"
\dd "no.such.database"."no.such.schema"."no.such.object.description"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.object.description"
\dD "no.such.database"."no.such.schema"."no.such.domain"
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index dcdbd4fc020..fbc416eb02e 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 \dCN
+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();
+
+---- \dCN shows constraints
+\dCN con_*
+\dCNcfnptue con_*
+\dCN+ con_*
+\dCNcfnptue+ con_*
+\dCNc con_*
+\dCNc+ con_*
+\dCNf con_*
+\dCNf+ con_*
+\dCNn con_*
+\dCNn+ con_*
+\dCNp con_*
+\dCNp+ con_*
+\dCNt con_*
+\dCNt+ con_*
+\dCNu con_*
+\dCNu+ con_*
+\dCNe con_*
+\dCNe+ con_*
+\dCNx con_*
+\dCNx+ con_*
+\dCNS pg_constraint*
+\dCNS+ pg_constraint*
+\dCNcfnpueS pg_constraint*
+\dCNcfnpueS+ pg_constraint*
+\dCNcfnpueS+x pg_constraint*
+
+---- \dCN 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
+);
+\dCN+ notnulldomain*;
+
+---- Incorrect options will result in an error
+\dCNcz
+
+---- 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;
+\dCN con_*
+SET SEARCH_PATH TO public;
+\dCN con_*
+RESET search_path;
+
+---- test with inherits
+CREATE TABLE zoo_parent (
+ cage int PRIMARY KEY
+);
+
+CREATE TABLE zoo_child (
+ animal text
+) INHERITS (zoo_parent);
+
+\dCN+ 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);
+
+\dCN+ zoo_part*
+
+-- clean up for \dCN 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*
@@ -1686,6 +1790,8 @@ DROP FUNCTION psql_error;
\dC host.regression.pg_catalog.int8
\dC ).pg_catalog.int8
\dC nonesuch.pg_catalog.int8
+\dCN host.regression.public.constraint
+\dCN nonesuch.public.constraint
\dd host.regression.pg_catalog.pg_class
\dd [.pg_catalog.pg_class
\dd nonesuch.pg_catalog.pg_class
@@ -1784,6 +1890,7 @@ DROP FUNCTION psql_error;
\db "no.such.tablespace"
\dc "no.such.conversion"
\dC "no.such.cast"
+\dCN "no.such.constraint"
\dd "no.such.object.description"
\dD "no.such.domain"
\ddp "no.such.default.access.privilege"
@@ -1825,6 +1932,7 @@ DROP FUNCTION psql_error;
\db "no.such.schema"."no.such.tablespace"
\dc "no.such.schema"."no.such.conversion"
\dC "no.such.schema"."no.such.cast"
+\dCN "no.such.schema"."no.such.constraint"
\dd "no.such.schema"."no.such.object.description"
\dD "no.such.schema"."no.such.domain"
\ddp "no.such.schema"."no.such.default.access.privilege"
@@ -1859,6 +1967,7 @@ DROP FUNCTION psql_error;
\da regression."no.such.schema"."no.such.aggregate.function"
\dc regression."no.such.schema"."no.such.conversion"
\dC regression."no.such.schema"."no.such.cast"
+\dCN regression."no.such.schema"."no.such.constraint"
\dd regression."no.such.schema"."no.such.object.description"
\dD regression."no.such.schema"."no.such.domain"
\di regression."no.such.schema"."no.such.index.relation"
@@ -1883,6 +1992,7 @@ DROP FUNCTION psql_error;
\da "no.such.database"."no.such.schema"."no.such.aggregate.function"
\dc "no.such.database"."no.such.schema"."no.such.conversion"
\dC "no.such.database"."no.such.schema"."no.such.cast"
+\dCN "no.such.database"."no.such.schema"."no.such.constraint"
\dd "no.such.database"."no.such.schema"."no.such.object.description"
\dD "no.such.database"."no.such.schema"."no.such.domain"
\ddp "no.such.database"."no.such.schema"."no.such.default.access.privilege"
--
2.43.5