psql \dh: List High-Level (Root) Tables and Indexes

From c7b7f39c171648f3193cb9d61a4815ac32b779e3 Mon Sep 17 00:00:00 2001
From: Sadeq <3616518+msdousti@users.noreply.github.com>
Date: Sun, 23 Feb 2025 21:37:51 +0100
Subject: [PATCH 2/2] Add id to varlistentry

---
 doc/src/sgml/ref/psql-ref.sgml | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index b1ed4a3bfaa..17b55b47d50 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1871,7 +1871,7 @@ SELECT $1 \parse stmt1
         </listitem>
       </varlistentry>
 
-      <varlistentry>
+      <varlistentry id="app-psql-meta-command-dh">
         <term><literal>\dh[it+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <listitem>
         <para>
-- 
2.43.0

From ad81b71fe67174aebd7f6c02a82aa0bbbbab88ff Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Sat, 22 Feb 2025 23:16:44 +0100
Subject: [PATCH v2] psql \dh: list high-level tables and indexes

---
 doc/src/sgml/ref/psql-ref.sgml     |  22 ++++
 src/bin/psql/command.c             |  18 +++
 src/bin/psql/describe.c            | 196 +++++++++++++++++++++++++++++
 src/bin/psql/describe.h            |   3 +
 src/bin/psql/help.c                |   1 +
 src/bin/psql/tab-complete.in.c     |  43 ++++++-
 src/test/regress/expected/psql.out |  59 +++++++++
 src/test/regress/sql/psql.sql      |  13 ++
 8 files changed, 354 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 3edbd65e46c..17b55b47d50 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1871,6 +1871,28 @@ SELECT $1 \parse stmt1
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-dh">
+        <term><literal>\dh[it+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <listitem>
+        <para>
+        Lists high-level (root) relations. This means relations that are
+        either not partitioned, or partitioned and have no parent.
+        If <replaceable class="parameter">pattern</replaceable>
+        is specified, only entries whose name matches the pattern are listed.
+        The modifiers <literal>t</literal> (tables) and <literal>i</literal>
+        (indexes) can be appended to the command, filtering the kind of
+        relations to list.  By default, high-level tables and indexes are
+        listed.
+        </para>
+
+        <para>
+        If <literal>+</literal> is appended to the command, the size of the
+        relation (when it's not partitioned), or the sum of the sizes of
+        the table's partitions (when the relation is partitioned) is also
+        displayed, along with the associated description.
+        </para>
+        </listitem>
+      </varlistentry>
 
       <varlistentry id="app-psql-meta-command-dl-lc">
         <term><literal>\dl[x+]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0f27bf7a91f..9fff1864010 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1117,6 +1117,24 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				/* no longer distinct from \du */
 				success = describeRoles(pattern, show_verbose, show_system);
 				break;
+			case 'h':
+				/* high-level (i.e., root) tables and indexes */
+				{
+					switch (cmd[2])
+					{
+						case '\0':
+						case '+':
+						case 't':
+						case 'i':
+						case 'x':
+							success = listRootTables(&cmd[2], pattern, show_verbose);
+							break;
+						default:
+							status = PSQL_CMD_UNKNOWN;
+							break;
+					}
+				}
+				break;
 			case 'l':
 				success = listLargeObjects(show_verbose);
 				break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..33270650511 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4247,6 +4247,202 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	return true;
 }
 
+/*
+ * \dh
+ * Takes an optional regexp to select particular relations
+ *
+ * As with \d, you can specify the kinds of relations you want:
+ *
+ * t for tables
+ * i for indexes
+ *
+ * and you can mix and match them in any order.
+ */
+bool
+listRootTables(const char *reltypes, const char *pattern, bool verbose)
+{
+	bool		showTables = strchr(reltypes, 't') != NULL;
+	bool		showIndexes = strchr(reltypes, 'i') != NULL;
+	PQExpBufferData buf;
+	PQExpBufferData title;
+	PGresult   *res;
+	printQueryOpt myopt = pset.popt;
+	bool		translate_columns[] = {false, false, false, false, false, false, false, false};
+	const char *tabletitle;
+	bool		mixed_output = false;
+
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (pset.sversion < 100000)
+	{
+		char		sverbuf[32];
+
+		pg_log_error("The server (version %s) does not support declarative table partitioning.",
+					 formatPGVersionNumber(pset.sversion, false,
+										   sverbuf, sizeof(sverbuf)));
+		return true;
+	}
+
+	/* If no relation kind was selected, show them all */
+	if (!showTables && !showIndexes)
+		showTables = showIndexes = true;
+
+	if (showIndexes && !showTables)
+		tabletitle = _("List of root indexes");	/* \dhi */
+	else if (showTables && !showIndexes)
+		tabletitle = _("List of root tables");	/* \dht */
+	else
+	{
+		/* show all kinds */
+		tabletitle = _("List of root relations");
+		mixed_output = true;
+	}
+
+	initPQExpBuffer(&buf);
+
+	printfPQExpBuffer(&buf,
+					  "SELECT n.nspname as \"%s\",\n"
+					  "  c.relname as \"%s\",\n"
+					  "  pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
+					  gettext_noop("Schema"),
+					  gettext_noop("Name"),
+					  gettext_noop("Owner"));
+
+	if (mixed_output)
+	{
+		appendPQExpBuffer(&buf,
+						  ",\n  CASE c.relkind"
+						  " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
+						  " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
+						  " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
+						  " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
+						  " END as \"%s\"",
+						  gettext_noop("table"),
+						  gettext_noop("partitioned table"),
+						  gettext_noop("index"),
+						  gettext_noop("partitioned index"),
+						  gettext_noop("Type"));
+
+		translate_columns[3] = true;
+	}
+
+	if (showIndexes)
+		appendPQExpBuffer(&buf,
+						  ",\n c2.oid::pg_catalog.regclass as \"%s\"",
+						  gettext_noop("Table"));
+
+	if (verbose)
+	{
+		/*
+		 * Table access methods were introduced in v12, and can be set on
+		 * partitioned tables since v17.
+		 */
+		appendPQExpBuffer(&buf, ",\n  am.amname as \"%s\"",
+						  gettext_noop("Access method"));
+
+		/* Sizes of all partitions are considered in this case. */
+		appendPQExpBuffer(&buf,
+							",\n  pg_catalog.pg_size_pretty(GREATEST(pg_catalog.pg_table_size(c.oid), s.tps)) as \"%s\"",
+							gettext_noop("Total size"));
+
+		appendPQExpBuffer(&buf,
+						  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
+						  gettext_noop("Description"));
+	}
+
+	appendPQExpBufferStr(&buf,
+						 "\nFROM pg_catalog.pg_class c"
+						 "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
+
+	if (showIndexes)
+		appendPQExpBufferStr(&buf,
+							 "\n     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
+							 "\n     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
+
+	if (verbose)
+	{
+		appendPQExpBufferStr(&buf,
+							 "\n     LEFT JOIN pg_catalog.pg_am am ON c.relam = am.oid");
+
+		if (pset.sversion < 120000)
+		{
+			appendPQExpBufferStr(&buf,
+								 ",\n     LATERAL (WITH RECURSIVE d\n"
+								 "                AS (SELECT inhrelid AS oid, 1 AS level\n"
+								 "                      FROM pg_catalog.pg_inherits\n"
+								 "                     WHERE inhparent = c.oid\n"
+								 "                    UNION ALL\n"
+								 "                    SELECT inhrelid, level + 1\n"
+								 "                      FROM pg_catalog.pg_inherits i\n"
+								 "                           JOIN d ON i.inhparent = d.oid)\n"
+								 "                SELECT sum(pg_catalog.pg_table_size(d.oid)) AS tps\n"
+								 "               FROM d) s");
+		}
+		else
+		{
+			/* PostgreSQL 12 has pg_partition_tree function */
+			appendPQExpBufferStr(&buf,
+								 ",\n     LATERAL (SELECT sum(pg_catalog.pg_table_size(ppt.relid)) AS tps"
+								 "\n              FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
+		}
+	}
+
+	appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
+	if (showTables)
+	{
+		appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) ",");
+		appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
+	}
+	if (showIndexes)
+	{
+		appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) ",");
+		appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_INDEX) ",");
+	}
+	appendPQExpBufferStr(&buf, "''");	/* dummy */
+	appendPQExpBufferStr(&buf, ")\n");
+
+	appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
+	if (!pattern)
+		appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
+							 "      AND n.nspname !~ '^pg_toast'\n"
+							 "      AND n.nspname <> 'information_schema'\n");
+
+	if (!validateSQLNamePattern(&buf, pattern, true, false,
+								"n.nspname", "c.relname", NULL,
+								"pg_catalog.pg_table_is_visible(c.oid)",
+								NULL, 3))
+	{
+		termPQExpBuffer(&buf);
+		return false;
+	}
+
+	appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s\"Name\";",
+					  mixed_output ? "\"Type\" DESC, " : "");
+
+	res = PSQLexec(buf.data);
+	termPQExpBuffer(&buf);
+	if (!res)
+		return false;
+
+	initPQExpBuffer(&title);
+	appendPQExpBufferStr(&title, tabletitle);
+
+	myopt.title = title.data;
+	myopt.translate_header = true;
+	myopt.translate_columns = translate_columns;
+	myopt.n_translate_columns = lengthof(translate_columns);
+
+	printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+	termPQExpBuffer(&title);
+
+	PQclear(res);
+	return true;
+}
+
+
 /*
  * \dP
  * Takes an optional regexp to select particular relations
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 18ecaa60949..6abd7ba31b6 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -70,6 +70,9 @@ extern bool listAllDbs(const char *pattern, bool verbose);
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
 
+/* \dh */
+extern bool listRootTables(const char *reltypes, const char *pattern, bool verbose);
+
 /* \dP */
 extern bool listPartitionedTables(const char *reltypes, const char *pattern, bool verbose);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 714b8619233..3f75be4644f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -254,6 +254,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dFp[x+] [PATTERN]     list text search parsers\n");
 	HELP0("  \\dFt[x+] [PATTERN]     list text search templates\n");
 	HELP0("  \\dg[Sx+] [PATTERN]     list roles\n");
+	HELP0("  \\dh[tix+] [PATTERN]    list high-level (root) relations\n");
 	HELP0("  \\di[Sx+] [PATTERN]     list indexes\n");
 	HELP0("  \\dl[x+]                list large objects, same as \\lo_list\n");
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..2e0ff93b69a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -691,6 +691,16 @@ static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.result = "c.relname",
 };
 
+static const SchemaQuery Query_for_list_of_root_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_tables_for_constraint = {
 	.catname = "pg_catalog.pg_class c, pg_catalog.pg_constraint con",
 	.selcondition = "c.oid=con.conrelid and c.relkind IN ("
@@ -795,6 +805,16 @@ static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.result = "c.relname",
 };
 
+static const SchemaQuery Query_for_list_of_root_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 
 /* All relations */
 static const SchemaQuery Query_for_list_of_relations = {
@@ -814,6 +834,19 @@ static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.result = "c.relname",
 };
 
+/* root relations */
+static const SchemaQuery Query_for_list_of_root_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_operator_families = {
 	.catname = "pg_catalog.pg_opfamily c",
 	.viscondition = "pg_catalog.pg_opfamily_is_visible(c.oid)",
@@ -1880,7 +1913,8 @@ psql_completion(const char *text, int start, int end)
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
 		"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
-		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
+		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\dh", "\\dhi", "\\dht",
+		"\\di", "\\dl", "\\dL",
 		"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
@@ -5269,8 +5303,15 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dFt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_templates);
 	/* must be at end of \dF alternatives: */
+
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
+	else if (TailMatchesCS("\\dhi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_root_indexes);
+	else if (TailMatchesCS("\\dht*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_root_tables);
+	else if (TailMatchesCS("\\dh*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_root_relations);
 
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..7ba2fff3919 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5017,6 +5017,33 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only root relations should be displayed
+\dh test*apple*
+                                       List of root relations
+  Schema  |         Name          |           Owner           |       Type        |      Table      
+----------+-----------------------+---------------------------+-------------------+-----------------
+ testpart | testtable_apple       | regress_partitioning_role | table             | 
+ testpart | testpart_apple        | regress_partitioning_role | partitioned table | 
+ testpart | testpart_apple_index  | regress_partitioning_role | partitioned index | testpart_apple
+ testpart | testtable_apple_index | regress_partitioning_role | index             | testtable_apple
+(4 rows)
+
+\dht test*apple*
+                  List of root tables
+  Schema  |      Name       |           Owner           
+----------+-----------------+---------------------------
+ testpart | testpart_apple  | regress_partitioning_role
+ testpart | testtable_apple | regress_partitioning_role
+(2 rows)
+
+\dhi test*apple*
+                              List of root indexes
+  Schema  |         Name          |           Owner           |      Table      
+----------+-----------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5038,6 +5065,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5106,6 +5134,37 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only root relations should be displayed
+\dht
+                List of root tables
+  Schema  |    Name    |           Owner           
+----------+------------+---------------------------
+ testpart | parent_tab | regress_partitioning_role
+(1 row)
+
+\dhi
+                       List of root indexes
+  Schema  |     Name     |           Owner           |   Table    
+----------+--------------+---------------------------+------------
+ testpart | parent_index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dh testpart.*
+                                List of root relations
+  Schema  |     Name     |           Owner           |       Type        |   Table    
+----------+--------------+---------------------------+-------------------+------------
+ testpart | parent_tab   | regress_partitioning_role | partitioned table | 
+ testpart | parent_index | regress_partitioning_role | partitioned index | parent_tab
+(2 rows)
+
+\dh
+                                List of root relations
+  Schema  |     Name     |           Owner           |       Type        |   Table    
+----------+--------------+---------------------------+-------------------+------------
+ testpart | parent_tab   | regress_partitioning_role | partitioned table | 
+ testpart | parent_index | regress_partitioning_role | partitioned index | parent_tab
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 97d1be3aac3..5606f70a106 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1257,6 +1257,11 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only root relations should be displayed
+\dh test*apple*
+\dht test*apple*
+\dhi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1280,6 +1285,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1291,6 +1297,13 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only root relations should be displayed
+\dht
+\dhi
+
+\dh testpart.*
+\dh
+
 drop table parent_tab cascade;
 
 drop schema testpart;
-- 
2.43.0

From d8fde4b05eee95089548384c07b59304f2fecc1c Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Tue, 25 Feb 2025 23:51:16 +0100
Subject: [PATCH v3] Add metacommand dN to psql

---
 doc/src/sgml/ref/psql-ref.sgml     | 10 ++--
 src/bin/psql/command.c             |  1 +
 src/bin/psql/describe.c            | 49 ++++++++++++++----
 src/bin/psql/help.c                |  1 +
 src/bin/psql/tab-complete.in.c     | 43 +++++++++++++++-
 src/test/regress/expected/psql.out | 81 ++++++++++++++++++++++++++++++
 src/test/regress/sql/psql.sql      | 15 ++++++
 7 files changed, 184 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cedccc14129..ea124e5a4d5 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1635,6 +1635,7 @@ SELECT $1 \parse stmt1
       <varlistentry id="app-psql-meta-command-de">
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1643,15 +1644,16 @@ SELECT $1 \parse stmt1
         <listitem>
         <para>
         In this group of commands, the letters <literal>E</literal>,
-        <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
-        <literal>t</literal>, and <literal>v</literal>
-        stand for foreign table, index, materialized view,
+        <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+        , <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes.
+        tables and indexes, and <literal>\dNti</literal> lists
+        tables and indexes that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0f27bf7a91f..cf65df42459 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1163,6 +1163,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case 'i':
 			case 's':
 			case 'E':
+			case 'N':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
 			case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..ba1d6c09bc9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4016,6 +4016,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	bool		showMatViews = strchr(tabtypes, 'm') != NULL;
 	bool		showSeq = strchr(tabtypes, 's') != NULL;
 	bool		showForeign = strchr(tabtypes, 'E') != NULL;
+	bool		showNoPartitions = strchr(tabtypes, 'N') != NULL;
 
 	int			ntypes;
 	PQExpBufferData buf;
@@ -4023,13 +4024,33 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	printQueryOpt myopt = pset.popt;
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
+	char	   *no_partition_description = showNoPartitions ? " non-partition" : "";
+
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (showNoPartitions && pset.sversion < 100000)
+	{
+		char		sverbuf[32];
+
+		pg_log_error("The server (version %s) does not support declarative table partitioning.",
+					 formatPGVersionNumber(pset.sversion, false,
+										   sverbuf, sizeof(sverbuf)));
+		return true;
+	}
 
 	/* Count the number of explicitly-requested relation types */
 	ntypes = showTables + showIndexes + showViews + showMatViews +
 		showSeq + showForeign;
-	/* If none, we default to \dtvmsE (but see also command.c) */
+
 	if (ntypes == 0)
-		showTables = showViews = showMatViews = showSeq = showForeign = true;
+	{
+		if (showNoPartitions)
+			showTables = showIndexes = true;
+		else
+			/* If none, we default to \dtvmsE (but see also command.c) */
+			showTables = showViews = showMatViews = showSeq = showForeign = true;
+	}
 
 	initPQExpBuffer(&buf);
 
@@ -4155,6 +4176,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
+	if (showNoPartitions)
+		appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
 								"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4181,14 +4205,14 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		if (pattern)
 		{
 			if (ntypes != 1)
-				pg_log_error("Did not find any relations named \"%s\".",
-							 pattern);
+				pg_log_error("Did not find any%s relations named \"%s\".",
+							 no_partition_description, pattern);
 			else if (showTables)
-				pg_log_error("Did not find any tables named \"%s\".",
-							 pattern);
+				pg_log_error("Did not find any%s tables named \"%s\".",
+							 no_partition_description, pattern);
 			else if (showIndexes)
-				pg_log_error("Did not find any indexes named \"%s\".",
-							 pattern);
+				pg_log_error("Did not find any%s indexes named \"%s\".",
+							 no_partition_description, pattern);
 			else if (showViews)
 				pg_log_error("Did not find any views named \"%s\".",
 							 pattern);
@@ -4208,11 +4232,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		else
 		{
 			if (ntypes != 1)
-				pg_log_error("Did not find any relations.");
+				pg_log_error("Did not find any%s relations.", no_partition_description);
 			else if (showTables)
-				pg_log_error("Did not find any tables.");
+				pg_log_error("Did not find any%s tables.", no_partition_description);
 			else if (showIndexes)
-				pg_log_error("Did not find any indexes.");
+				pg_log_error("Did not find any%s indexes.", no_partition_description);
 			else if (showViews)
 				pg_log_error("Did not find any views.");
 			else if (showMatViews)
@@ -4228,8 +4252,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	else
 	{
 		myopt.title =
+			(ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
 			(ntypes != 1) ? _("List of relations") :
+			(showTables && showNoPartitions) ? _("List of tables (no partitions)") :
 			(showTables) ? _("List of tables") :
+			(showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
 			(showIndexes) ? _("List of indexes") :
 			(showViews) ? _("List of views") :
 			(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 714b8619233..186699638c7 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -259,6 +259,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list relation, table, index (no partitions) \n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..33ac14f10e2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -683,6 +683,17 @@ static const SchemaQuery Query_for_list_of_tables = {
 	.result = "c.relname",
 };
 
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -787,6 +798,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
 	.result = "c.relname",
 };
 
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -804,6 +826,19 @@ static const SchemaQuery Query_for_list_of_relations = {
 	.result = "c.relname",
 };
 
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 /* partitioned relations */
 static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -1881,7 +1916,7 @@ psql_completion(const char *text, int start, int end)
 		"\\db", "\\dc", "\\dconfig", "\\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",
+		"\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5272,6 +5307,8 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
 
+	else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
 	else if (TailMatchesCS("\\dL*"))
@@ -5295,6 +5332,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
 	else if (TailMatchesCS("\\ds*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
 	else if (TailMatchesCS("\\dt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
 	else if (TailMatchesCS("\\dT*"))
@@ -5317,6 +5356,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
 
 	/* must be at end of \d alternatives: */
+	else if (TailMatchesCS("\\dN*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
 	else if (TailMatchesCS("\\d*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..9394a677248 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5017,6 +5017,47 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only non-partition relations should be displayed
+\dN
+                                  List of relations (no partitions)
+  Schema  |          Name          |       Type        |           Owner           |      Table       
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple         | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index   | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_orange_index  | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple        | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index  | index             | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange       | table             | regress_partitioning_role | 
+ testpart | testtable_orange_index | index             | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+                                 List of relations (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple       | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+                       List of tables (no partitions)
+  Schema  |      Name       |       Type        |           Owner           
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple  | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table             | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+                                  List of indexes (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5038,6 +5079,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5106,6 +5148,45 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only non-partition relations should be displayed
+\dNt
+                    List of tables (no partitions)
+  Schema  |    Name    |       Type        |           Owner           
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+                           List of indexes (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN testpart.*
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 97d1be3aac3..2f0f7bd1dc0 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1257,6 +1257,12 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1280,6 +1286,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1291,6 +1298,14 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
 drop table parent_tab cascade;
 
 drop schema testpart;
-- 
2.43.0

From 29a59cb46665312810c9a73a6964afd3db0e03f6 Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Wed, 26 Feb 2025 01:23:52 +0100
Subject: [PATCH v4 2/2] Apply Greg's comments

---
 doc/src/sgml/ref/psql-ref.sgml |  8 ++++----
 src/bin/psql/describe.c        | 19 +++++++++----------
 src/bin/psql/help.c            |  2 +-
 3 files changed, 14 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ea124e5a4d5..37b516fc558 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1635,8 +1635,8 @@ SELECT $1 \parse stmt1
       <varlistentry id="app-psql-meta-command-de">
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
-        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dv[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1645,15 +1645,15 @@ SELECT $1 \parse stmt1
         <para>
         In this group of commands, the letters <literal>E</literal>,
         <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
-        , <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
         stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes, and <literal>\dNti</literal> lists
-        tables and indexes that are not partitions of any other relation.
+        tables and indexes, and <literal>\dNt</literal> lists
+        tables that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ba1d6c09bc9..d4be468de55 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4024,7 +4024,6 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	printQueryOpt myopt = pset.popt;
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
-	char	   *no_partition_description = showNoPartitions ? " non-partition" : "";
 
 	/*
 	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
@@ -4205,14 +4204,14 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		if (pattern)
 		{
 			if (ntypes != 1)
-				pg_log_error("Did not find any%s relations named \"%s\".",
-							 no_partition_description, pattern);
+				pg_log_error("Did not find any relations named \"%s\".",
+							 pattern);
 			else if (showTables)
-				pg_log_error("Did not find any%s tables named \"%s\".",
-							 no_partition_description, pattern);
+				pg_log_error("Did not find any tables named \"%s\".",
+							 pattern);
 			else if (showIndexes)
-				pg_log_error("Did not find any%s indexes named \"%s\".",
-							 no_partition_description, pattern);
+				pg_log_error("Did not find any indexes named \"%s\".",
+							 pattern);
 			else if (showViews)
 				pg_log_error("Did not find any views named \"%s\".",
 							 pattern);
@@ -4232,11 +4231,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		else
 		{
 			if (ntypes != 1)
-				pg_log_error("Did not find any%s relations.", no_partition_description);
+				pg_log_error("Did not find any relations.");
 			else if (showTables)
-				pg_log_error("Did not find any%s tables.", no_partition_description);
+				pg_log_error("Did not find any tables.");
 			else if (showIndexes)
-				pg_log_error("Did not find any%s indexes.", no_partition_description);
+				pg_log_error("Did not find any indexes.");
 			else if (showViews)
 				pg_log_error("Did not find any views.");
 			else if (showMatViews)
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 186699638c7..09f98da98f2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -259,7 +259,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
-	HELP0("  \\dN[Sx+] [PATTERN]     list relation, table, index (no partitions) \n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list tables and indexes (no partitions)\n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
-- 
2.43.0

From d8fde4b05eee95089548384c07b59304f2fecc1c Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Tue, 25 Feb 2025 23:51:16 +0100
Subject: [PATCH v4 1/2] Add metacommand dN to psql

---
 doc/src/sgml/ref/psql-ref.sgml     | 10 ++--
 src/bin/psql/command.c             |  1 +
 src/bin/psql/describe.c            | 49 ++++++++++++++----
 src/bin/psql/help.c                |  1 +
 src/bin/psql/tab-complete.in.c     | 43 +++++++++++++++-
 src/test/regress/expected/psql.out | 81 ++++++++++++++++++++++++++++++
 src/test/regress/sql/psql.sql      | 15 ++++++
 7 files changed, 184 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cedccc14129..ea124e5a4d5 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1635,6 +1635,7 @@ SELECT $1 \parse stmt1
       <varlistentry id="app-psql-meta-command-de">
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1643,15 +1644,16 @@ SELECT $1 \parse stmt1
         <listitem>
         <para>
         In this group of commands, the letters <literal>E</literal>,
-        <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
-        <literal>t</literal>, and <literal>v</literal>
-        stand for foreign table, index, materialized view,
+        <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+        , <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes.
+        tables and indexes, and <literal>\dNti</literal> lists
+        tables and indexes that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0f27bf7a91f..cf65df42459 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1163,6 +1163,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case 'i':
 			case 's':
 			case 'E':
+			case 'N':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
 			case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..ba1d6c09bc9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4016,6 +4016,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	bool		showMatViews = strchr(tabtypes, 'm') != NULL;
 	bool		showSeq = strchr(tabtypes, 's') != NULL;
 	bool		showForeign = strchr(tabtypes, 'E') != NULL;
+	bool		showNoPartitions = strchr(tabtypes, 'N') != NULL;
 
 	int			ntypes;
 	PQExpBufferData buf;
@@ -4023,13 +4024,33 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	printQueryOpt myopt = pset.popt;
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
+	char	   *no_partition_description = showNoPartitions ? " non-partition" : "";
+
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (showNoPartitions && pset.sversion < 100000)
+	{
+		char		sverbuf[32];
+
+		pg_log_error("The server (version %s) does not support declarative table partitioning.",
+					 formatPGVersionNumber(pset.sversion, false,
+										   sverbuf, sizeof(sverbuf)));
+		return true;
+	}
 
 	/* Count the number of explicitly-requested relation types */
 	ntypes = showTables + showIndexes + showViews + showMatViews +
 		showSeq + showForeign;
-	/* If none, we default to \dtvmsE (but see also command.c) */
+
 	if (ntypes == 0)
-		showTables = showViews = showMatViews = showSeq = showForeign = true;
+	{
+		if (showNoPartitions)
+			showTables = showIndexes = true;
+		else
+			/* If none, we default to \dtvmsE (but see also command.c) */
+			showTables = showViews = showMatViews = showSeq = showForeign = true;
+	}
 
 	initPQExpBuffer(&buf);
 
@@ -4155,6 +4176,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
+	if (showNoPartitions)
+		appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
 								"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4181,14 +4205,14 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		if (pattern)
 		{
 			if (ntypes != 1)
-				pg_log_error("Did not find any relations named \"%s\".",
-							 pattern);
+				pg_log_error("Did not find any%s relations named \"%s\".",
+							 no_partition_description, pattern);
 			else if (showTables)
-				pg_log_error("Did not find any tables named \"%s\".",
-							 pattern);
+				pg_log_error("Did not find any%s tables named \"%s\".",
+							 no_partition_description, pattern);
 			else if (showIndexes)
-				pg_log_error("Did not find any indexes named \"%s\".",
-							 pattern);
+				pg_log_error("Did not find any%s indexes named \"%s\".",
+							 no_partition_description, pattern);
 			else if (showViews)
 				pg_log_error("Did not find any views named \"%s\".",
 							 pattern);
@@ -4208,11 +4232,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 		else
 		{
 			if (ntypes != 1)
-				pg_log_error("Did not find any relations.");
+				pg_log_error("Did not find any%s relations.", no_partition_description);
 			else if (showTables)
-				pg_log_error("Did not find any tables.");
+				pg_log_error("Did not find any%s tables.", no_partition_description);
 			else if (showIndexes)
-				pg_log_error("Did not find any indexes.");
+				pg_log_error("Did not find any%s indexes.", no_partition_description);
 			else if (showViews)
 				pg_log_error("Did not find any views.");
 			else if (showMatViews)
@@ -4228,8 +4252,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	else
 	{
 		myopt.title =
+			(ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
 			(ntypes != 1) ? _("List of relations") :
+			(showTables && showNoPartitions) ? _("List of tables (no partitions)") :
 			(showTables) ? _("List of tables") :
+			(showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
 			(showIndexes) ? _("List of indexes") :
 			(showViews) ? _("List of views") :
 			(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 714b8619233..186699638c7 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -259,6 +259,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list relation, table, index (no partitions) \n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..33ac14f10e2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -683,6 +683,17 @@ static const SchemaQuery Query_for_list_of_tables = {
 	.result = "c.relname",
 };
 
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -787,6 +798,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
 	.result = "c.relname",
 };
 
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -804,6 +826,19 @@ static const SchemaQuery Query_for_list_of_relations = {
 	.result = "c.relname",
 };
 
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 /* partitioned relations */
 static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -1881,7 +1916,7 @@ psql_completion(const char *text, int start, int end)
 		"\\db", "\\dc", "\\dconfig", "\\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",
+		"\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5272,6 +5307,8 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
 
+	else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
 	else if (TailMatchesCS("\\dL*"))
@@ -5295,6 +5332,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
 	else if (TailMatchesCS("\\ds*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
 	else if (TailMatchesCS("\\dt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
 	else if (TailMatchesCS("\\dT*"))
@@ -5317,6 +5356,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
 
 	/* must be at end of \d alternatives: */
+	else if (TailMatchesCS("\\dN*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
 	else if (TailMatchesCS("\\d*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..9394a677248 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5017,6 +5017,47 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only non-partition relations should be displayed
+\dN
+                                  List of relations (no partitions)
+  Schema  |          Name          |       Type        |           Owner           |      Table       
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple         | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index   | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_orange_index  | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple        | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index  | index             | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange       | table             | regress_partitioning_role | 
+ testpart | testtable_orange_index | index             | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+                                 List of relations (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple       | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+                       List of tables (no partitions)
+  Schema  |      Name       |       Type        |           Owner           
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple  | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table             | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+                                  List of indexes (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5038,6 +5079,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5106,6 +5148,45 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only non-partition relations should be displayed
+\dNt
+                    List of tables (no partitions)
+  Schema  |    Name    |       Type        |           Owner           
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+                           List of indexes (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN testpart.*
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 97d1be3aac3..2f0f7bd1dc0 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1257,6 +1257,12 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1280,6 +1286,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1291,6 +1298,14 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
 drop table parent_tab cascade;
 
 drop schema testpart;
-- 
2.43.0

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cedccc14129..37b516fc558 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1636,6 +1636,7 @@ SELECT $1 \parse stmt1
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dv[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1643,15 +1644,16 @@ SELECT $1 \parse stmt1
         <listitem>
         <para>
         In this group of commands, the letters <literal>E</literal>,
-        <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
-        <literal>t</literal>, and <literal>v</literal>
-        stand for foreign table, index, materialized view,
+        <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+        <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes.
+        tables and indexes, and <literal>\dNt</literal> lists
+        tables that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0f27bf7a91f..cf65df42459 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1163,6 +1163,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case 'i':
 			case 's':
 			case 'E':
+			case 'N':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
 			case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..d4be468de55 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4016,6 +4016,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	bool		showMatViews = strchr(tabtypes, 'm') != NULL;
 	bool		showSeq = strchr(tabtypes, 's') != NULL;
 	bool		showForeign = strchr(tabtypes, 'E') != NULL;
+	bool		showNoPartitions = strchr(tabtypes, 'N') != NULL;
 
 	int			ntypes;
 	PQExpBufferData buf;
@@ -4024,12 +4025,31 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
 
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (showNoPartitions && pset.sversion < 100000)
+	{
+		char		sverbuf[32];
+
+		pg_log_error("The server (version %s) does not support declarative table partitioning.",
+					 formatPGVersionNumber(pset.sversion, false,
+										   sverbuf, sizeof(sverbuf)));
+		return true;
+	}
+
 	/* Count the number of explicitly-requested relation types */
 	ntypes = showTables + showIndexes + showViews + showMatViews +
 		showSeq + showForeign;
-	/* If none, we default to \dtvmsE (but see also command.c) */
+
 	if (ntypes == 0)
-		showTables = showViews = showMatViews = showSeq = showForeign = true;
+	{
+		if (showNoPartitions)
+			showTables = showIndexes = true;
+		else
+			/* If none, we default to \dtvmsE (but see also command.c) */
+			showTables = showViews = showMatViews = showSeq = showForeign = true;
+	}
 
 	initPQExpBuffer(&buf);
 
@@ -4155,6 +4175,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
+	if (showNoPartitions)
+		appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
 								"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4228,8 +4251,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	else
 	{
 		myopt.title =
+			(ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
 			(ntypes != 1) ? _("List of relations") :
+			(showTables && showNoPartitions) ? _("List of tables (no partitions)") :
 			(showTables) ? _("List of tables") :
+			(showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
 			(showIndexes) ? _("List of indexes") :
 			(showViews) ? _("List of views") :
 			(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 714b8619233..09f98da98f2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -259,6 +259,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list tables and indexes (no partitions)\n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..33ac14f10e2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -683,6 +683,17 @@ static const SchemaQuery Query_for_list_of_tables = {
 	.result = "c.relname",
 };
 
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -787,6 +798,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
 	.result = "c.relname",
 };
 
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -804,6 +826,19 @@ static const SchemaQuery Query_for_list_of_relations = {
 	.result = "c.relname",
 };
 
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 /* partitioned relations */
 static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -1881,7 +1916,7 @@ psql_completion(const char *text, int start, int end)
 		"\\db", "\\dc", "\\dconfig", "\\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",
+		"\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5272,6 +5307,8 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
 
+	else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
 	else if (TailMatchesCS("\\dL*"))
@@ -5295,6 +5332,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
 	else if (TailMatchesCS("\\ds*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
 	else if (TailMatchesCS("\\dt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
 	else if (TailMatchesCS("\\dT*"))
@@ -5317,6 +5356,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
 
 	/* must be at end of \d alternatives: */
+	else if (TailMatchesCS("\\dN*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
 	else if (TailMatchesCS("\\d*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..9394a677248 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5017,6 +5017,47 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only non-partition relations should be displayed
+\dN
+                                  List of relations (no partitions)
+  Schema  |          Name          |       Type        |           Owner           |      Table       
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple         | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index   | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_orange_index  | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple        | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index  | index             | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange       | table             | regress_partitioning_role | 
+ testpart | testtable_orange_index | index             | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+                                 List of relations (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple       | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+                       List of tables (no partitions)
+  Schema  |      Name       |       Type        |           Owner           
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple  | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table             | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+                                  List of indexes (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5038,6 +5079,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5106,6 +5148,45 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only non-partition relations should be displayed
+\dNt
+                    List of tables (no partitions)
+  Schema  |    Name    |       Type        |           Owner           
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+                           List of indexes (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN testpart.*
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 97d1be3aac3..2f0f7bd1dc0 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1257,6 +1257,12 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1280,6 +1286,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1291,6 +1298,14 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
 drop table parent_tab cascade;
 
 drop schema testpart;

From d17bf733834f0c9976e4cd53888e9523d27d8495 Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Fri, 28 Mar 2025 00:21:41 +0100
Subject: [PATCH v6] psql acommand for non-partitioned tables & indexes

This patch introduces the new letter N for the \d (describe)
metacommand of psql. Using this command, one can list all
the tables and indexes that are not partitioned. Using \dtN and
\diN, one can further limit the disabled objects to non-partitioned
tables, respectively, non-partitioned indexes.
---
 doc/src/sgml/ref/psql-ref.sgml     | 10 ++--
 src/bin/psql/command.c             |  1 +
 src/bin/psql/describe.c            | 30 ++++++++++-
 src/bin/psql/help.c                |  1 +
 src/bin/psql/tab-complete.in.c     | 43 +++++++++++++++-
 src/test/regress/expected/psql.out | 81 ++++++++++++++++++++++++++++++
 src/test/regress/sql/psql.sql      | 15 ++++++
 7 files changed, 174 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f7c8bc16a7f..693ccd201c4 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1636,6 +1636,7 @@ SELECT $1 \parse stmt1
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dv[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1643,15 +1644,16 @@ SELECT $1 \parse stmt1
         <listitem>
         <para>
         In this group of commands, the letters <literal>E</literal>,
-        <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
-        <literal>t</literal>, and <literal>v</literal>
-        stand for foreign table, index, materialized view,
+        <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+        <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes.
+        tables and indexes, and <literal>\dNt</literal> lists
+        tables that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a8a13c2b88b..7df521a89ad 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1166,6 +1166,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case 'i':
 			case 's':
 			case 'E':
+			case 'N':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
 			case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index bf565afcc4e..af2455916c6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4016,6 +4016,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	bool		showMatViews = strchr(tabtypes, 'm') != NULL;
 	bool		showSeq = strchr(tabtypes, 's') != NULL;
 	bool		showForeign = strchr(tabtypes, 'E') != NULL;
+	bool		showNoPartitions = strchr(tabtypes, 'N') != NULL;
 
 	int			ntypes;
 	PQExpBufferData buf;
@@ -4024,12 +4025,31 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
 
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (showNoPartitions && pset.sversion < 100000)
+	{
+		char		sverbuf[32];
+
+		pg_log_error("The server (version %s) does not support declarative table partitioning.",
+					 formatPGVersionNumber(pset.sversion, false,
+										   sverbuf, sizeof(sverbuf)));
+		return true;
+	}
+
 	/* Count the number of explicitly-requested relation types */
 	ntypes = showTables + showIndexes + showViews + showMatViews +
 		showSeq + showForeign;
-	/* If none, we default to \dtvmsE (but see also command.c) */
+
 	if (ntypes == 0)
-		showTables = showViews = showMatViews = showSeq = showForeign = true;
+	{
+		if (showNoPartitions)
+			showTables = showIndexes = true;
+		else
+			/* If none, we default to \dtvmsE (but see also command.c) */
+			showTables = showViews = showMatViews = showSeq = showForeign = true;
+	}
 
 	initPQExpBuffer(&buf);
 
@@ -4155,6 +4175,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
+	if (showNoPartitions)
+		appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
 								"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4228,8 +4251,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	else
 	{
 		myopt.title =
+			(ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
 			(ntypes != 1) ? _("List of relations") :
+			(showTables && showNoPartitions) ? _("List of tables (no partitions)") :
 			(showTables) ? _("List of tables") :
+			(showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
 			(showIndexes) ? _("List of indexes") :
 			(showViews) ? _("List of views") :
 			(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index fe96e3e1de9..4b335363e9d 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -260,6 +260,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list tables and indexes (no partitions)\n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 98951aef82c..485edd6a5b2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -683,6 +683,17 @@ static const SchemaQuery Query_for_list_of_tables = {
 	.result = "c.relname",
 };
 
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -787,6 +798,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
 	.result = "c.relname",
 };
 
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -804,6 +826,19 @@ static const SchemaQuery Query_for_list_of_relations = {
 	.result = "c.relname",
 };
 
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 /* partitioned relations */
 static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -1881,7 +1916,7 @@ psql_completion(const char *text, int start, int end)
 		"\\db", "\\dc", "\\dconfig", "\\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",
+		"\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5281,6 +5316,8 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
 
+	else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
 	else if (TailMatchesCS("\\dL*"))
@@ -5304,6 +5341,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
 	else if (TailMatchesCS("\\ds*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
 	else if (TailMatchesCS("\\dt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
 	else if (TailMatchesCS("\\dT*"))
@@ -5326,6 +5365,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
 
 	/* must be at end of \d alternatives: */
+	else if (TailMatchesCS("\\dN*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
 	else if (TailMatchesCS("\\d*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index b1d12585eae..6ffcc50fd2f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5018,6 +5018,47 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only non-partition relations should be displayed
+\dN
+                                  List of relations (no partitions)
+  Schema  |          Name          |       Type        |           Owner           |      Table       
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple         | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index   | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_orange_index  | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple        | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index  | index             | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange       | table             | regress_partitioning_role | 
+ testpart | testtable_orange_index | index             | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+                                 List of relations (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple       | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+                       List of tables (no partitions)
+  Schema  |      Name       |       Type        |           Owner           
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple  | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table             | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+                                  List of indexes (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5039,6 +5080,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5107,6 +5149,45 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only non-partition relations should be displayed
+\dNt
+                    List of tables (no partitions)
+  Schema  |    Name    |       Type        |           Owner           
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+                           List of indexes (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN testpart.*
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1a8a83462f0..55ab2143e41 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1258,6 +1258,12 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1281,6 +1287,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1292,6 +1299,14 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
 drop table parent_tab cascade;
 
 drop schema testpart;
-- 
2.39.5 (Apple Git-154)

From c9904b5c04ec992b51f1e20400a47016a51dadff Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Mon, 28 Jul 2025 00:56:43 +0200
Subject: [PATCH v7] psql acommand for non-partitioned tables & indexes

This patch introduces the new letter N for the \d (describe)
metacommand of psql. Using this command, one can list all
the tables and indexes that are not partitioned. Using \dtN and
\diN, one can further limit the disabled objects to non-partitioned
tables, respectively, non-partitioned indexes.
---
 doc/src/sgml/ref/psql-ref.sgml     | 10 ++--
 src/bin/psql/command.c             |  1 +
 src/bin/psql/describe.c            | 26 +++++++++-
 src/bin/psql/help.c                |  1 +
 src/bin/psql/tab-complete.in.c     | 43 +++++++++++++++-
 src/test/regress/expected/psql.out | 81 ++++++++++++++++++++++++++++++
 src/test/regress/sql/psql.sql      | 15 ++++++
 7 files changed, 170 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 95f4cac2467..010a82a20b5 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1645,6 +1645,7 @@ SELECT $1 \parse stmt1
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dv[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1652,15 +1653,16 @@ SELECT $1 \parse stmt1
         <listitem>
         <para>
         In this group of commands, the letters <literal>E</literal>,
-        <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
-        <literal>t</literal>, and <literal>v</literal>
-        stand for foreign table, index, materialized view,
+        <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+        <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes.
+        tables and indexes, and <literal>\dNt</literal> lists
+        tables that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0a55901b14e..31acda1412f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1169,6 +1169,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case 'i':
 			case 's':
 			case 'E':
+			case 'N':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
 			case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index dd25d2fe7b8..eadd18abab1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3997,6 +3997,7 @@ describeRoleGrants(const char *pattern, bool showSystem)
  * tabtypes is an array of characters, specifying what info is desired:
  * t - tables
  * i - indexes
+ * N - no partitions (only applies to tables and indexes)
  * v - views
  * m - materialized views
  * s - sequences
@@ -4012,6 +4013,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	bool		showMatViews = strchr(tabtypes, 'm') != NULL;
 	bool		showSeq = strchr(tabtypes, 's') != NULL;
 	bool		showForeign = strchr(tabtypes, 'E') != NULL;
+	bool		showNoPartitions = strchr(tabtypes, 'N') != NULL;
 
 	int			ntypes;
 	PQExpBufferData buf;
@@ -4020,12 +4022,26 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
 
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (showNoPartitions && pset.sversion < 100000)
+	{
+		showNoPartitions = false;
+	}
+
 	/* Count the number of explicitly-requested relation types */
 	ntypes = showTables + showIndexes + showViews + showMatViews +
 		showSeq + showForeign;
-	/* If none, we default to \dtvmsE (but see also command.c) */
+
 	if (ntypes == 0)
-		showTables = showViews = showMatViews = showSeq = showForeign = true;
+	{
+		if (showNoPartitions)
+			showTables = showIndexes = true;
+		else
+			/* If none, we default to \dtvmsE (but see also command.c) */
+			showTables = showViews = showMatViews = showSeq = showForeign = true;
+	}
 
 	initPQExpBuffer(&buf);
 
@@ -4151,6 +4167,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
+	if (showNoPartitions)
+		appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
 								"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4224,8 +4243,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	else
 	{
 		myopt.title =
+			(ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
 			(ntypes != 1) ? _("List of relations") :
+			(showTables && showNoPartitions) ? _("List of tables (no partitions)") :
 			(showTables) ? _("List of tables") :
+			(showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
 			(showIndexes) ? _("List of indexes") :
 			(showViews) ? _("List of views") :
 			(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 8c62729a0d1..652cdb7f72f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -247,6 +247,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list tables and indexes (no partitions)\n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index f2734f8f273..7c0123b61c8 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -683,6 +683,17 @@ static const SchemaQuery Query_for_list_of_tables = {
 	.result = "c.relname",
 };
 
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -787,6 +798,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
 	.result = "c.relname",
 };
 
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -804,6 +826,19 @@ static const SchemaQuery Query_for_list_of_relations = {
 	.result = "c.relname",
 };
 
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 /* partitioned relations */
 static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -1894,7 +1929,7 @@ psql_completion(const char *text, int start, int end)
 		"\\db", "\\dc", "\\dconfig", "\\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",
+		"\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5302,6 +5337,8 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
 
+	else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
 	else if (TailMatchesCS("\\dL*"))
@@ -5325,6 +5362,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
 	else if (TailMatchesCS("\\ds*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
 	else if (TailMatchesCS("\\dt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
 	else if (TailMatchesCS("\\dT*"))
@@ -5347,6 +5386,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
 
 	/* must be at end of \d alternatives: */
+	else if (TailMatchesCS("\\dN*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
 	else if (TailMatchesCS("\\d*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 236eba2540e..25b728ba433 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5018,6 +5018,47 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only non-partition relations should be displayed
+\dN
+                                  List of relations (no partitions)
+  Schema  |          Name          |       Type        |           Owner           |      Table       
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple         | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index   | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_orange_index  | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple        | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index  | index             | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange       | table             | regress_partitioning_role | 
+ testpart | testtable_orange_index | index             | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+                                 List of relations (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple       | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+                       List of tables (no partitions)
+  Schema  |      Name       |       Type        |           Owner           
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple  | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table             | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+                                  List of indexes (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5039,6 +5080,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5107,6 +5149,45 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only non-partition relations should be displayed
+\dNt
+                    List of tables (no partitions)
+  Schema  |    Name    |       Type        |           Owner           
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+                           List of indexes (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN testpart.*
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index e2e31245439..9f5b447e5e2 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1258,6 +1258,12 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1281,6 +1287,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1292,6 +1299,14 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
 drop table parent_tab cascade;
 
 drop schema testpart;
-- 
2.43.0

From f3888e0c7d9a0f02834e84a6a07d91eefbd5f97c Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Mon, 28 Jul 2025 00:56:43 +0200
Subject: [PATCH v8] psql: Listing "not-partition" tables and indexes

This patch introduces the new letter N for the \d (describe)
metacommand of psql. Using this command, one can take a look at
the high-level table (resp. index) structure, meaning those that
are NOT (thus, "N") a partition of any other table (resp. index).

This is useful when the database has many (tens or hundreds) of
partitioned tables. Simply issuing \d (or \dt or \di) will list so
many objects, and getting a high-level peek at the database design
becomes hard.

Using the new \dN, \dtN (= \dNt) or \diN (= \dNi), one can more
easily observe the high-level design of the tables and indexes.

For instance, if the database has two tables t and u, and these
are further partitioned as follows:

t
+-- t0
+-- t1
+-- t2

u
t
+-- u0
+-- u1

Then issuing \dtN will only list t and u, which is close to the
"logical" database design, and spares the further "physical" design,
where partitions t0, t1, t2, u0, and u1 would also be listed.
---
 doc/src/sgml/ref/psql-ref.sgml     | 10 ++--
 src/bin/psql/command.c             |  1 +
 src/bin/psql/describe.c            | 26 +++++++++-
 src/bin/psql/help.c                |  1 +
 src/bin/psql/tab-complete.in.c     | 43 +++++++++++++++-
 src/test/regress/expected/psql.out | 81 ++++++++++++++++++++++++++++++
 src/test/regress/sql/psql.sql      | 15 ++++++
 7 files changed, 170 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f56c70263e0..35cc058c952 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1645,6 +1645,7 @@ SELECT $1 \parse stmt1
         <term><literal>\dE[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\di[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dm[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+        <term><literal>\dN[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\ds[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dt[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <term><literal>\dv[Sx+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
@@ -1652,15 +1653,16 @@ SELECT $1 \parse stmt1
         <listitem>
         <para>
         In this group of commands, the letters <literal>E</literal>,
-        <literal>i</literal>, <literal>m</literal>, <literal>s</literal>,
-        <literal>t</literal>, and <literal>v</literal>
-        stand for foreign table, index, materialized view,
+        <literal>i</literal>, <literal>m</literal>, <literal>N</literal>,
+        <literal>s</literal>, <literal>t</literal>, and <literal>v</literal>
+        stand for foreign table, index, materialized view, no partitions,
         sequence, table, and view,
         respectively.
         You can specify any or all of
         these letters, in any order, to obtain a listing of objects
         of these types.  For example, <literal>\dti</literal> lists
-        tables and indexes.
+        tables and indexes, and <literal>\dNt</literal> lists
+        tables that are not partitions of any other relation.
         If <literal>x</literal> is appended to the command name, the results
         are displayed in expanded mode.
         If <literal>+</literal> is
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4a2976dddf0..bf5f61e5d3e 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1190,6 +1190,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case 'i':
 			case 's':
 			case 'E':
+			case 'N':
 				success = listTables(&cmd[1], pattern, show_verbose, show_system);
 				break;
 			case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 36f24502842..5e31ceba437 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4031,6 +4031,7 @@ describeRoleGrants(const char *pattern, bool showSystem)
  * tabtypes is an array of characters, specifying what info is desired:
  * t - tables
  * i - indexes
+ * N - no partitions (only applies to tables and indexes)
  * v - views
  * m - materialized views
  * s - sequences
@@ -4046,6 +4047,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	bool		showMatViews = strchr(tabtypes, 'm') != NULL;
 	bool		showSeq = strchr(tabtypes, 's') != NULL;
 	bool		showForeign = strchr(tabtypes, 'E') != NULL;
+	bool		showNoPartitions = strchr(tabtypes, 'N') != NULL;
 
 	int			ntypes;
 	PQExpBufferData buf;
@@ -4054,12 +4056,26 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	int			cols_so_far;
 	bool		translate_columns[] = {false, false, true, false, false, false, false, false, false};
 
+	/*
+	 * Note: Declarative table partitioning is only supported as of Pg 10.0.
+	 */
+	if (showNoPartitions && pset.sversion < 100000)
+	{
+		showNoPartitions = false;
+	}
+
 	/* Count the number of explicitly-requested relation types */
 	ntypes = showTables + showIndexes + showViews + showMatViews +
 		showSeq + showForeign;
-	/* If none, we default to \dtvmsE (but see also command.c) */
+
 	if (ntypes == 0)
-		showTables = showViews = showMatViews = showSeq = showForeign = true;
+	{
+		if (showNoPartitions)
+			showTables = showIndexes = true;
+		else
+			/* If none, we default to \dtvmsE (but see also command.c) */
+			showTables = showViews = showMatViews = showSeq = showForeign = true;
+	}
 
 	initPQExpBuffer(&buf);
 
@@ -4185,6 +4201,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 							 "      AND n.nspname !~ '^pg_toast'\n"
 							 "      AND n.nspname <> 'information_schema'\n");
 
+	if (showNoPartitions)
+		appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
 	if (!validateSQLNamePattern(&buf, pattern, true, false,
 								"n.nspname", "c.relname", NULL,
 								"pg_catalog.pg_table_is_visible(c.oid)",
@@ -4258,8 +4277,11 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	else
 	{
 		myopt.title =
+			(ntypes != 1 && showNoPartitions) ? _("List of relations (no partitions)") :
 			(ntypes != 1) ? _("List of relations") :
+			(showTables && showNoPartitions) ? _("List of tables (no partitions)") :
 			(showTables) ? _("List of tables") :
+			(showIndexes && showNoPartitions) ? _("List of indexes (no partitions)") :
 			(showIndexes) ? _("List of indexes") :
 			(showViews) ? _("List of views") :
 			(showMatViews) ? _("List of materialized views") :
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ec0b49b957b..c0e7fea78ae 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -251,6 +251,7 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\dL[Sx+] [PATTERN]     list procedural languages\n");
 	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
 	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\dN[Sx+] [PATTERN]     list tables and indexes (no partitions)\n");
 	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
 	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index b1ff6f6cd94..efc3637c956 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -703,6 +703,17 @@ static const SchemaQuery Query_for_list_of_tables = {
 	.result = "c.relname",
 };
 
+/* All tables EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_tables = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_tables = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
@@ -807,6 +818,17 @@ static const SchemaQuery Query_for_list_of_indexes = {
 	.result = "c.relname",
 };
 
+/* All indexes EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_indexes = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.catname = "pg_catalog.pg_class c",
 	.selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
@@ -824,6 +846,19 @@ static const SchemaQuery Query_for_list_of_relations = {
 	.result = "c.relname",
 };
 
+/* All relations EXCEPT those marked as relispartition = true */
+static const SchemaQuery Query_for_list_of_not_relispartition_relations = {
+	.catname = "pg_catalog.pg_class c",
+	.selcondition = "c.relispartition = false AND "
+	"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+	CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+	CppAsString2(RELKIND_INDEX) ", "
+	CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+	.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+	.namespace = "c.relnamespace",
+	.result = "c.relname",
+};
+
 /* partitioned relations */
 static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -1928,7 +1963,7 @@ psql_completion(const char *text, int start, int end)
 		"\\db", "\\dc", "\\dconfig", "\\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",
+		"\\dm", "\\dn", "\\dN", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -5456,6 +5491,8 @@ match_previous_words(int pattern_id,
 	else if (TailMatchesCS("\\dF*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
 
+	else if (TailMatchesCS("\\diN*") || TailMatchesCS("\\dNi*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_indexes);
 	else if (TailMatchesCS("\\di*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
 	else if (TailMatchesCS("\\dL*"))
@@ -5479,6 +5516,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_VERSIONED_QUERY(Query_for_list_of_subscriptions);
 	else if (TailMatchesCS("\\ds*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	else if (TailMatchesCS("\\dtN*") || TailMatchesCS("\\dNt*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_tables);
 	else if (TailMatchesCS("\\dt*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
 	else if (TailMatchesCS("\\dT*"))
@@ -5501,6 +5540,8 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
 
 	/* must be at end of \d alternatives: */
+	else if (TailMatchesCS("\\dN*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_not_relispartition_relations);
 	else if (TailMatchesCS("\\d*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c8f3932edf0..ce4dbb0bec8 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5052,6 +5052,47 @@ create index testpart_orange_index on testpart_orange(logdate);
  testpart | testpart_apple_index | regress_partitioning_role |             | testpart_apple
 (1 row)
 
+-- only non-partition relations should be displayed
+\dN
+                                  List of relations (no partitions)
+  Schema  |          Name          |       Type        |           Owner           |      Table       
+----------+------------------------+-------------------+---------------------------+------------------
+ testpart | testpart_apple         | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index   | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testpart_orange        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_orange_index  | partitioned index | regress_partitioning_role | testpart_orange
+ testpart | testtable_apple        | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index  | index             | regress_partitioning_role | testtable_apple
+ testpart | testtable_orange       | table             | regress_partitioning_role | 
+ testpart | testtable_orange_index | index             | regress_partitioning_role | testtable_orange
+(8 rows)
+
+\dN test*apple*
+                                 List of relations (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple        | partitioned table | regress_partitioning_role | 
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple       | table             | regress_partitioning_role | 
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(4 rows)
+
+\dNt test*apple*
+                       List of tables (no partitions)
+  Schema  |      Name       |       Type        |           Owner           
+----------+-----------------+-------------------+---------------------------
+ testpart | testpart_apple  | partitioned table | regress_partitioning_role
+ testpart | testtable_apple | table             | regress_partitioning_role
+(2 rows)
+
+\dNi test*apple*
+                                  List of indexes (no partitions)
+  Schema  |         Name          |       Type        |           Owner           |      Table      
+----------+-----------------------+-------------------+---------------------------+-----------------
+ testpart | testpart_apple_index  | partitioned index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | index             | regress_partitioning_role | testtable_apple
+(2 rows)
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -5073,6 +5114,7 @@ create table child_30_35 partition of child_30_40
 create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
 \dPt
             List of partitioned tables
   Schema  |    Name    |           Owner           
@@ -5141,6 +5183,45 @@ insert into parent_tab values (generate_series(30,39));
  testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
 (4 rows)
 
+-- only non-partition relations should be displayed
+\dNt
+                    List of tables (no partitions)
+  Schema  |    Name    |       Type        |           Owner           
+----------+------------+-------------------+---------------------------
+ testpart | parent_tab | partitioned table | regress_partitioning_role
+(1 row)
+
+\dNi
+                           List of indexes (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dNit
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
+\dN testpart.*
+                          List of relations (no partitions)
+  Schema  |     Name     |       Type        |           Owner           |   Table    
+----------+--------------+-------------------+---------------------------+------------
+ testpart | parent_index | partitioned index | regress_partitioning_role | parent_tab
+ testpart | parent_tab   | partitioned table | regress_partitioning_role | 
+(2 rows)
+
 drop table parent_tab cascade;
 drop schema testpart;
 set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index dcdbd4fc020..7ec82d67602 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1276,6 +1276,12 @@ create index testpart_orange_index on testpart_orange(logdate);
 \dPt test*apple*
 \dPi test*apple*
 
+-- only non-partition relations should be displayed
+\dN
+\dN test*apple*
+\dNt test*apple*
+\dNi test*apple*
+
 drop table testtable_apple;
 drop table testtable_orange;
 drop table testpart_apple;
@@ -1299,6 +1305,7 @@ create table child_35_40 partition of child_30_40
    for values from (35) to (40);
 insert into parent_tab values (generate_series(30,39));
 
+-- only partition related object should be displayed
 \dPt
 \dPi
 
@@ -1310,6 +1317,14 @@ insert into parent_tab values (generate_series(30,39));
 \dPn
 \dPn testpart.*
 
+-- only non-partition relations should be displayed
+\dNt
+\dNi
+\dNit
+\dN
+
+\dN testpart.*
+
 drop table parent_tab cascade;
 
 drop schema testpart;
-- 
2.50.1 (Apple Git-155)