Dear Matt,
Thanks a lot for the nice suggestion. Please find attached the version 7 of
the patch, which incorporates your suggestion: It now disregards the N in
\dN[ti] if pset.sversion < 100000.
Best Regards,
Sadeq
PS: Received an error from the mailer, with the following error message, so
removed the mentioned email and resending the email. Sorry if you receive
it twice.
This email has been blocked from posting to the lists,
and for this reason your email has not been delivered to
the list. If you wish to post to the list, please remove
dweht...@gmail.com from the address fields of your email,
and try again.
On Fri, Jun 20, 2025 at 10:16 PM Matt Dailis <dweht...@gmail.com> wrote:
> Hi Sadeq,
>
> > * Support for the various \d ("describe") commands. Note that the
> current
> > * expectation is that all functions in this file will succeed when
> working
> > * with servers of versions 9.2 and up. It's okay to omit irrelevant
> > * information for an old server, but not to fail outright. (But failing
> > * against a pre-9.2 server is allowed.)
> >
> > I'm just following the instructions here so as not to break anything
> unwanted, and you can see for instance \dP is doing the same.
>
> One possible interpretation of this comment is that a command should
> try to return as much relevant information as it reasonably can for an
> older postgres version. The patch currently treats \dtiN as an error
> for pset.sversion < 100000. What do you think about making \dtiN
> behaving identically to \dti on older postgres versions? We know that
> the older database definitely has no partitions, and the user is
> telling us that they'd like to see everything except for the
> partitions, so it seems reasonable to me to ignore the N option in
> that case.
>
> Best,
> Matt Dailis
>
From c9904b5c04ec992b51f1e20400a47016a51dadff Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdou...@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
