v7-0001-Add-list-constraints-meta-command-dCN-on-psql.patch
application/octet-stream
Filename: v7-0001-Add-list-constraints-meta-command-dCN-on-psql.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v7-0001
Subject: Add list constraints meta-command \dCN on psql
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/psql-ref.sgml | 20 | 1 |
| src/bin/psql/command.c | 22 | 1 |
| src/bin/psql/describe.c | 147 | 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 | 543 | 0 |
| src/test/regress/sql/psql.sql | 149 | 0 |
From b3191e26588a9d8e57be0fd97d282a750ad179c5 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatattsu@gmail.com>
Date: Wed, 21 Jan 2026 17:23:08 +0900
Subject: [PATCH v7] Add list constraints meta-command \dCN on psql
\dCN shows all kinds 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!)
- Fix commit/error/help messages, document and Simplify the code
(based on Chao's comments. Thanks!)
- Support domain constraints
- Add test cases for domain constraints and create type
- Address Alvalo's comments (Thanks!)
- Change the columns and their order (and the sort order accordingly):
- Schema | Table | Type | Name | Definition
- Toggle definition verbosity with the + option
- Add a test case like: \dCN cust*.order*
- Display all constraint types in full and in lowercase
- Add test cases for temporal constraints
---
doc/src/sgml/ref/psql-ref.sgml | 21 +-
src/bin/psql/command.c | 23 +-
src/bin/psql/describe.c | 147 ++++++++
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 | 543 +++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 149 ++++++++
8 files changed, 890 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f56c70263e0..9fd99ca0178 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,26 @@ 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. Note that table names are
+ not shown for domain constraints.
+ </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..66016f81ddb 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5104,6 +5104,153 @@ 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;
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (strlen(contypes) != strspn(contypes, dCN_options))
+ {
+ pg_log_error("\\dCN only takes [cfnptue][Sx+] as 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 */
+ showAllkinds = !(showCheck || showForeign || showNotnull || showPrimary || showTrigger || showUnique || showExclusion);
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname AS \"%s\", \n"
+ " c.relname AS \"%s\", \n"
+ " CASE cns.contype \n"
+ " WHEN 'c' THEN 'check' \n"
+ " WHEN 'f' THEN 'foreign key' \n"
+ " WHEN 'n' THEN 'not-null' \n"
+ " WHEN 'p' THEN 'primary key' \n"
+ " WHEN 't' THEN 'trigger' \n"
+ " WHEN 'u' THEN 'unique' \n"
+ " WHEN 'x' THEN 'exclusion' \n"
+ " END AS \"%s\", \n"
+ " cns.conname AS \"%s\"",
+ gettext_noop("Schema"),
+ gettext_noop("Table"),
+ gettext_noop("Type"),
+ gettext_noop("Name")
+ );
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.pg_get_constraintdef(cns.oid, true) AS \"%s\" ",
+ gettext_noop("Definition")
+ );
+
+ /*
+ * conrelid <> 0: table constraints including column constraints
+ * contypid > 0: domain constraints
+ */
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_constraint cns \n"
+ " JOIN pg_catalog.pg_namespace n ON n.oid = cns.connamespace \n"
+ " LEFT JOIN pg_catalog.pg_type t ON t.oid = cns.contypid \n"
+ " LEFT JOIN pg_catalog.pg_class c on c.oid = cns.conrelid \n"
+ "WHERE ((cns.conrelid <> 0 AND pg_catalog.pg_table_is_visible(cns.conrelid)) \n"
+ " OR (cns.contypid > 0 AND pg_catalog.pg_type_is_visible(t.oid))) \n"
+ );
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf,
+ " AND n.nspname <> 'pg_catalog' \n"
+ " AND n.nspname <> 'information_schema' \n");
+
+ if (!validateSQLNamePattern(&buf, pattern,
+ true, false,
+ "n.nspname", "cns.conname", NULL,
+ NULL, NULL, 3))
+ {
+ termPQExpBuffer(&buf);
+ return false;
+ }
+
+ if (!showAllkinds)
+ {
+ appendPQExpBufferStr(&buf, " AND cns.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, 3, 4, 5;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
+
+ 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..4da936a988c 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..3d9e79ba6c3 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5388,6 +5388,525 @@ 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 | Table | Type | Name
+--------+-------+-------------+----------------------------
+ public | con_c | not-null | con_c_primary_col_not_null
+ public | con_c | primary key | con_c_pkey
+ public | con_p | check | con_p_check_col_check
+ public | con_p | exclusion | con_p_exclusion
+ public | con_p | foreign key | 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 | primary key | con_p_pkey
+ public | con_p | trigger | con_p_trigger
+ public | con_p | unique | con_p_unique_col_key
+(10 rows)
+
+\dCNcfnptue con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+-------------+----------------------------
+ public | con_c | not-null | con_c_primary_col_not_null
+ public | con_c | primary key | con_c_pkey
+ public | con_p | check | con_p_check_col_check
+ public | con_p | exclusion | con_p_exclusion
+ public | con_p | foreign key | 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 | primary key | con_p_pkey
+ public | con_p | trigger | con_p_trigger
+ public | con_p | unique | con_p_unique_col_key
+(10 rows)
+
+\dCN+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+-------------+----------------------------+---------------------------------------------------------
+ public | con_c | not-null | con_c_primary_col_not_null | NOT NULL primary_col
+ public | con_c | primary key | con_c_pkey | PRIMARY KEY (primary_col)
+ public | con_p | check | con_p_check_col_check | CHECK (check_col >= 0)
+ public | con_p | exclusion | con_p_exclusion | EXCLUDE USING btree (exclusion_col WITH =)
+ public | con_p | foreign key | con_p_foreign_col_fkey | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col)
+ public | con_p | not-null | con_p_notnull_col_not_null | NOT NULL notnull_col
+ public | con_p | not-null | con_p_primary_col_not_null | NOT NULL primary_col
+ public | con_p | primary key | con_p_pkey | PRIMARY KEY (primary_col)
+ public | con_p | trigger | con_p_trigger | TRIGGER
+ public | con_p | unique | con_p_unique_col_key | UNIQUE (unique_col)
+(10 rows)
+
+\dCNcfnptue+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+-------------+----------------------------+---------------------------------------------------------
+ public | con_c | not-null | con_c_primary_col_not_null | NOT NULL primary_col
+ public | con_c | primary key | con_c_pkey | PRIMARY KEY (primary_col)
+ public | con_p | check | con_p_check_col_check | CHECK (check_col >= 0)
+ public | con_p | exclusion | con_p_exclusion | EXCLUDE USING btree (exclusion_col WITH =)
+ public | con_p | foreign key | con_p_foreign_col_fkey | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col)
+ public | con_p | not-null | con_p_notnull_col_not_null | NOT NULL notnull_col
+ public | con_p | not-null | con_p_primary_col_not_null | NOT NULL primary_col
+ public | con_p | primary key | con_p_pkey | PRIMARY KEY (primary_col)
+ public | con_p | trigger | con_p_trigger | TRIGGER
+ public | con_p | unique | con_p_unique_col_key | UNIQUE (unique_col)
+(10 rows)
+
+\dCNc con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+-------+-----------------------
+ public | con_p | check | con_p_check_col_check
+(1 row)
+
+\dCNc+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+-------+-----------------------+------------------------
+ public | con_p | check | con_p_check_col_check | CHECK (check_col >= 0)
+(1 row)
+
+\dCNf con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+-------------+------------------------
+ public | con_p | foreign key | con_p_foreign_col_fkey
+(1 row)
+
+\dCNf+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+-------------+------------------------+---------------------------------------------------------
+ public | con_p | foreign key | con_p_foreign_col_fkey | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col)
+(1 row)
+
+\dCNn con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+----------+----------------------------
+ public | con_c | not-null | con_c_primary_col_not_null
+ public | con_p | not-null | con_p_notnull_col_not_null
+ public | con_p | not-null | con_p_primary_col_not_null
+(3 rows)
+
+\dCNn+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+----------+----------------------------+----------------------
+ public | con_c | not-null | con_c_primary_col_not_null | NOT NULL primary_col
+ public | con_p | not-null | con_p_notnull_col_not_null | NOT NULL notnull_col
+ public | con_p | not-null | con_p_primary_col_not_null | NOT NULL primary_col
+(3 rows)
+
+\dCNp con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+-------------+------------
+ public | con_c | primary key | con_c_pkey
+ public | con_p | primary key | con_p_pkey
+(2 rows)
+
+\dCNp+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+-------------+------------+---------------------------
+ public | con_c | primary key | con_c_pkey | PRIMARY KEY (primary_col)
+ public | con_p | primary key | con_p_pkey | PRIMARY KEY (primary_col)
+(2 rows)
+
+\dCNt con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+---------+---------------
+ public | con_p | trigger | con_p_trigger
+(1 row)
+
+\dCNt+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+---------+---------------+------------
+ public | con_p | trigger | con_p_trigger | TRIGGER
+(1 row)
+
+\dCNu con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+--------+----------------------
+ public | con_p | unique | con_p_unique_col_key
+(1 row)
+
+\dCNu+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+--------+----------------------+---------------------
+ public | con_p | unique | con_p_unique_col_key | UNIQUE (unique_col)
+(1 row)
+
+\dCNe con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+-----------+-----------------
+ public | con_p | exclusion | con_p_exclusion
+(1 row)
+
+\dCNe+ con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+-----------+-----------------+--------------------------------------------
+ public | con_p | exclusion | con_p_exclusion | EXCLUDE USING btree (exclusion_col WITH =)
+(1 row)
+
+\dCNx con_*
+List of constraints
+-[ RECORD 1 ]----------------------
+Schema | public
+Table | con_c
+Type | not-null
+Name | con_c_primary_col_not_null
+-[ RECORD 2 ]----------------------
+Schema | public
+Table | con_c
+Type | primary key
+Name | con_c_pkey
+-[ RECORD 3 ]----------------------
+Schema | public
+Table | con_p
+Type | check
+Name | con_p_check_col_check
+-[ RECORD 4 ]----------------------
+Schema | public
+Table | con_p
+Type | exclusion
+Name | con_p_exclusion
+-[ RECORD 5 ]----------------------
+Schema | public
+Table | con_p
+Type | foreign key
+Name | con_p_foreign_col_fkey
+-[ RECORD 6 ]----------------------
+Schema | public
+Table | con_p
+Type | not-null
+Name | con_p_notnull_col_not_null
+-[ RECORD 7 ]----------------------
+Schema | public
+Table | con_p
+Type | not-null
+Name | con_p_primary_col_not_null
+-[ RECORD 8 ]----------------------
+Schema | public
+Table | con_p
+Type | primary key
+Name | con_p_pkey
+-[ RECORD 9 ]----------------------
+Schema | public
+Table | con_p
+Type | trigger
+Name | con_p_trigger
+-[ RECORD 10 ]---------------------
+Schema | public
+Table | con_p
+Type | unique
+Name | con_p_unique_col_key
+
+\dCNx+ con_*
+List of constraints
+-[ RECORD 1 ]-------------------------------------------------------
+Schema | public
+Table | con_c
+Type | not-null
+Name | con_c_primary_col_not_null
+Definition | NOT NULL primary_col
+-[ RECORD 2 ]-------------------------------------------------------
+Schema | public
+Table | con_c
+Type | primary key
+Name | con_c_pkey
+Definition | PRIMARY KEY (primary_col)
+-[ RECORD 3 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | check
+Name | con_p_check_col_check
+Definition | CHECK (check_col >= 0)
+-[ RECORD 4 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | exclusion
+Name | con_p_exclusion
+Definition | EXCLUDE USING btree (exclusion_col WITH =)
+-[ RECORD 5 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | foreign key
+Name | con_p_foreign_col_fkey
+Definition | FOREIGN KEY (foreign_col) REFERENCES con_c(primary_col)
+-[ RECORD 6 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | not-null
+Name | con_p_notnull_col_not_null
+Definition | NOT NULL notnull_col
+-[ RECORD 7 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | not-null
+Name | con_p_primary_col_not_null
+Definition | NOT NULL primary_col
+-[ RECORD 8 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | primary key
+Name | con_p_pkey
+Definition | PRIMARY KEY (primary_col)
+-[ RECORD 9 ]-------------------------------------------------------
+Schema | public
+Table | con_p
+Type | trigger
+Name | con_p_trigger
+Definition | TRIGGER
+-[ RECORD 10 ]------------------------------------------------------
+Schema | public
+Table | con_p
+Type | unique
+Name | con_p_unique_col_key
+Definition | UNIQUE (unique_col)
+
+\dCNS pg_constraint*
+ List of constraints
+ Schema | Table | Type | Name
+------------+---------------+-------------+-----------------------------------------------
+ pg_catalog | pg_constraint | not-null | pg_constraint_oid_not_null
+ pg_catalog | pg_constraint | primary key | pg_constraint_oid_index
+ pg_catalog | pg_constraint | unique | pg_constraint_conrelid_contypid_conname_index
+(3 rows)
+
+\dCNS+ pg_constraint*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+------------+---------------+-------------+-----------------------------------------------+--------------------------------------
+ pg_catalog | pg_constraint | not-null | pg_constraint_oid_not_null | NOT NULL oid
+ pg_catalog | pg_constraint | primary key | pg_constraint_oid_index | PRIMARY KEY (oid)
+ pg_catalog | pg_constraint | unique | pg_constraint_conrelid_contypid_conname_index | UNIQUE (conrelid, contypid, conname)
+(3 rows)
+
+\dCNcfnpueS pg_constraint*
+ List of constraints
+ Schema | Table | Type | Name
+------------+---------------+-------------+-----------------------------------------------
+ pg_catalog | pg_constraint | not-null | pg_constraint_oid_not_null
+ pg_catalog | pg_constraint | primary key | pg_constraint_oid_index
+ pg_catalog | pg_constraint | unique | pg_constraint_conrelid_contypid_conname_index
+(3 rows)
+
+\dCNcfnpueS+ pg_constraint*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+------------+---------------+-------------+-----------------------------------------------+--------------------------------------
+ pg_catalog | pg_constraint | not-null | pg_constraint_oid_not_null | NOT NULL oid
+ pg_catalog | pg_constraint | primary key | pg_constraint_oid_index | PRIMARY KEY (oid)
+ pg_catalog | pg_constraint | unique | pg_constraint_conrelid_contypid_conname_index | UNIQUE (conrelid, contypid, conname)
+(3 rows)
+
+\dCNcfnpueS+x pg_constraint*
+List of constraints
+-[ RECORD 1 ]---------------------------------------------
+Schema | pg_catalog
+Table | pg_constraint
+Type | not-null
+Name | pg_constraint_oid_not_null
+Definition | NOT NULL oid
+-[ RECORD 2 ]---------------------------------------------
+Schema | pg_catalog
+Table | pg_constraint
+Type | primary key
+Name | pg_constraint_oid_index
+Definition | PRIMARY KEY (oid)
+-[ RECORD 3 ]---------------------------------------------
+Schema | pg_catalog
+Table | pg_constraint
+Type | unique
+Name | pg_constraint_conrelid_contypid_conname_index
+Definition | UNIQUE (conrelid, contypid, conname)
+
+\dCN pg_con*.
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+------+------
+(0 rows)
+
+---- domain
+---- table name is not displayed because conrelid is zero
+CREATE DOMAIN hoge_domain integer
+ DEFAULT 10
+ NOT NULL
+ CHECK (VALUE > 0 AND VALUE < 100);
+CREATE TABLE table_using_domain (
+ col hoge_domain PRIMARY KEY
+);
+\dCN+ hoge*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-------+----------+----------------------+-----------------------------------
+ public | | check | hoge_domain_check | CHECK (VALUE > 0 AND VALUE < 100)
+ public | | not-null | hoge_domain_not_null | NOT NULL
+(2 rows)
+
+---- type
+CREATE TABLE table_using_type(
+ a INT,
+ b int42 NOT NULL
+);
+\dCN+ table_using_type*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+------------------+----------+-----------------------------+------------
+ public | table_using_type | not-null | table_using_type_b_not_null | NOT NULL b
+(1 row)
+
+---- Incorrect options will result in an error
+\dCNcz
+\dCN only takes [cfnptue][Sx+] as options
+---- 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 | Table | Type | Name
+------------+-----------------+-------------+--------------------------------------
+ con_schema | con_schema_test | not-null | con_schema_test_primary_col_not_null
+ con_schema | con_schema_test | primary key | con_schema_test_pkey
+ public | con_c | not-null | con_c_primary_col_not_null
+ public | con_c | primary key | con_c_pkey
+ public | con_p | check | con_p_check_col_check
+ public | con_p | exclusion | con_p_exclusion
+ public | con_p | foreign key | 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 | primary key | con_p_pkey
+ public | con_p | trigger | con_p_trigger
+ public | con_p | unique | con_p_unique_col_key
+(12 rows)
+
+\dCN con_*.con_*_pkey
+ List of constraints
+ Schema | Table | Type | Name
+------------+-----------------+-------------+----------------------
+ con_schema | con_schema_test | primary key | con_schema_test_pkey
+(1 row)
+
+SET SEARCH_PATH TO public;
+\dCN con_*
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+-------------+----------------------------
+ public | con_c | not-null | con_c_primary_col_not_null
+ public | con_c | primary key | con_c_pkey
+ public | con_p | check | con_p_check_col_check
+ public | con_p | exclusion | con_p_exclusion
+ public | con_p | foreign key | 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 | primary key | con_p_pkey
+ public | con_p | trigger | con_p_trigger
+ public | con_p | unique | con_p_unique_col_key
+(10 rows)
+
+RESET search_path;
+---- 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 | Table | Type | Name | Definition
+--------+------------+----------+--------------------------+---------------
+ public | zoo_child | not-null | zoo_parent_cage_not_null | NOT NULL cage
+ public | zoo_parent | not-null | zoo_parent_cage_not_null | NOT NULL cage
+(2 rows)
+
+---- 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 | Table | Type | Name | Definition
+--------+------------+-------------+------------------------+--------------------
+ public | zoo_part | not-null | zoo_part_cage_not_null | NOT NULL cage
+ public | zoo_part | primary key | zoo_part_pk | PRIMARY KEY (cage)
+ public | zoo_part_1 | not-null | zoo_part_cage_not_null | NOT NULL cage
+ public | zoo_part_1 | primary key | zoo_part_1_pkey | PRIMARY KEY (cage)
+(4 rows)
+
+---- temporal constraints
+CREATE TABLE temporal_con_pk (
+ id int4range,
+ valid_at daterange,
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_con_uq (
+ id int4range,
+ valid_at daterange,
+ UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_con_fk (
+ p_id int4range,
+ valid_at daterange,
+ FOREIGN KEY (p_id, PERIOD valid_at)
+ REFERENCES temporal_con_pk (id, PERIOD valid_at)
+);
+\dCN+ temporal_con_*
+ List of constraints
+ Schema | Table | Type | Name | Definition
+--------+-----------------+-------------+------------------------------------+-------------------------------------------------------------------------------------
+ public | temporal_con_fk | foreign key | temporal_con_fk_p_id_valid_at_fkey | FOREIGN KEY (p_id, PERIOD valid_at) REFERENCES temporal_con_pk(id, PERIOD valid_at)
+ public | temporal_con_pk | not-null | temporal_con_pk_id_not_null | NOT NULL id
+ public | temporal_con_pk | not-null | temporal_con_pk_valid_at_not_null | NOT NULL valid_at
+ public | temporal_con_pk | primary key | temporal_con_pk_pkey | PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ public | temporal_con_uq | unique | temporal_con_uq_id_valid_at_key | UNIQUE (id, valid_at WITHOUT OVERLAPS)
+(5 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_using_domain, table_using_type, zoo_parent, zoo_part CASCADE;
+NOTICE: drop cascades to table zoo_child
+DROP TABLE temporal_con_pk, temporal_con_uq, temporal_con_fk CASCADE;
+DROP FUNCTION trigger_hoge;
+DROP DOMAIN hoge_domain;
-- check \df, \do with argument specifications
\df *sqrt
List of functions
@@ -6106,6 +6625,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 +6862,12 @@ List of access methods
-------------+-------------+----------+-----------
(0 rows)
+\dCN "no.such.constraint"
+ List of constraints
+ Schema | Table | Type | Name
+--------+-------+------+------
+(0 rows)
+
\dd "no.such.object.description"
Object descriptions
Schema | Name | Object | Description
@@ -6549,6 +7078,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 | Table | Type | Name
+--------+-------+------+------
+(0 rows)
+
\dd "no.such.schema"."no.such.object.description"
Object descriptions
Schema | Name | Object | Description
@@ -6706,6 +7241,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 | Table | Type | Name
+--------+-------+------+------
+(0 rows)
+
\dd regression."no.such.schema"."no.such.object.description"
Object descriptions
Schema | Name | Object | Description
@@ -6823,6 +7364,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..a637348c3bf 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1347,6 +1347,149 @@ 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 pg_con*.
+
+---- domain
+---- table name is not displayed because conrelid is zero
+CREATE DOMAIN hoge_domain integer
+ DEFAULT 10
+ NOT NULL
+ CHECK (VALUE > 0 AND VALUE < 100);
+
+CREATE TABLE table_using_domain (
+ col hoge_domain PRIMARY KEY
+);
+
+\dCN+ hoge*
+
+---- type
+CREATE TABLE table_using_type(
+ a INT,
+ b int42 NOT NULL
+);
+
+\dCN+ table_using_type*
+
+---- Incorrect options will result in an error
+\dCNcz
+
+---- 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_*
+\dCN con_*.con_*_pkey
+SET SEARCH_PATH TO public;
+\dCN con_*
+RESET search_path;
+
+---- inherits
+CREATE TABLE zoo_parent (
+ cage int PRIMARY KEY
+);
+
+CREATE TABLE zoo_child (
+ animal text
+) INHERITS (zoo_parent);
+
+\dCN+ zoo_parent_cage_not_null
+
+---- 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*
+
+---- temporal constraints
+CREATE TABLE temporal_con_pk (
+ id int4range,
+ valid_at daterange,
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+CREATE TABLE temporal_con_uq (
+ id int4range,
+ valid_at daterange,
+ UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+CREATE TABLE temporal_con_fk (
+ p_id int4range,
+ valid_at daterange,
+ FOREIGN KEY (p_id, PERIOD valid_at)
+ REFERENCES temporal_con_pk (id, PERIOD valid_at)
+);
+
+\dCN+ temporal_con_*
+
+-- clean up for \dCN test cases
+DROP SCHEMA con_schema CASCADE;
+DROP TABLE con_p, con_c, table_using_domain, table_using_type, zoo_parent, zoo_part CASCADE;
+DROP TABLE temporal_con_pk, temporal_con_uq, temporal_con_fk CASCADE;
+DROP FUNCTION trigger_hoge;
+DROP DOMAIN hoge_domain;
+
-- check \df, \do with argument specifications
\df *sqrt
\df *sqrt num*
@@ -1686,6 +1829,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 +1929,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 +1971,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 +2006,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 +2031,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