> \dA{f,p,fo,fp,oc}
> Please explain what these are.
We adhere to the following logic
f - families
fo - operators in families
fp - procedures in families
p - access method properties
oc - operator classes
> I think this is two patches -- one being the \dip/\dicp part, the
> other
> the \dA additions. Let's deal with them separately?
The attached patches are applied sequentially: first 0003-
psql_add_am_info.patch, then 0003-psql_add_index_info.patch.
Best regards,
Sergey Cherkashin.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c134bca809..e25412b7ce 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -675,7 +675,7 @@
search and ordering purposes.)
</para>
- <table>
+ <table id="catalog-pg-amop-table">
<title><structname>pg_amop</structname> Columns</title>
<tgroup cols="4">
@@ -818,7 +818,7 @@
is one row for each support function belonging to an operator family.
</para>
- <table>
+ <table id="catalog-pg-amproc-table">
<title><structname>pg_amproc</structname> Columns</title>
<tgroup cols="4">
@@ -4421,7 +4421,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator classes are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opclass-table">
<title><structname>pg_opclass</structname> Columns</title>
<tgroup cols="4">
@@ -4683,7 +4683,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator families are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opfamily-table">
<title><structname>pg_opfamily</structname> Columns</title>
<tgroup cols="4">
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6e6d0f42d1..fcde01b2d4 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1204,6 +1204,105 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <literal>\dAf
+ [ <link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Lists operator families (<xref linkend="catalog-pg-opfamily-table"/>).
+ If <replaceable class="parameter">access-method-pattern</replaceable>
+ is specified, only families whose access method name matches the pattern
+ are shown. If
+ <replaceable class="parameter">operator-family-pattern</replaceable>
+ is specified, only operator families associated with whose name matches
+ the pattern are shown. If <literal>+</literal> is appended to the
+ command name, each operator family is listed with it's owner.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAfo
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Lists operators (<xref linkend="catalog-pg-amop-table"/>) associated
+ with access method operator families. If
+ <replaceable class="parameter">access-method-patttern</replaceable> is
+ specified, only operators associated with access method whose name
+ matches pattern are shown. If
+ <replaceable class="parameter">operator-family-pattern</replaceable> is
+ specified, only operators associated with families whose name matches
+ the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAfp
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ List procedures (<xref linkend="catalog-pg-amproc-table"/>) associated
+ with access method operator families.
+ If <replaceable class="parameter">access-method-patttern</replaceable>
+ is specified, only procedures associated with access method whose name
+ matches pattern are shown.
+ If <replaceable class="parameter">operator-family-pattern</replaceable>
+ is specified, only procedures associated with families whose name
+ matches the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAoc
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-class-pattern</replaceable></link>]]
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ Shows index access method operator classes listed in
+ <xref linkend="catalog-pg-opclass-table"/>.
+ If <replaceable class="parameter">access-method-patttern</replaceable>
+ is specified, only operator classes associated with access method whose
+ name matches pattern are shown.
+ If <replaceable class="parameter">operator-class-pattern</replaceable>
+ is specified, only procedures associated with families whose name
+ matches the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>\dAp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+
+ <listitem>
+ <para>
+ Shows access method properties listed in
+ <xref linkend="functions-info-indexam-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\db[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ee88e1ca5c..4d0f619186 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -719,7 +719,23 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
- success = describeAccessMethods(pattern, show_verbose);
+ {
+ char *pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);
+
+ if (strncmp(cmd, "dAp", 3) == 0)
+ success = describeAccessMethodProperties(pattern);
+ else if (strncmp(cmd, "dAfo", 4) == 0)
+ success = listFamilyClassOperators(pattern, pattern2);
+ else if (strncmp(cmd, "dAfp", 4) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2);
+ else if (strncmp(cmd, "dAf", 3) == 0)
+ success = listAccessMethodOperatorFamilies(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAoc", 4) == 0)
+ success = describeAccessMethodOperatorClasses(pattern, pattern2, show_verbose);
+ else
+ success = describeAccessMethods(pattern, show_verbose);
+ free(pattern2);
+ }
break;
case 'a':
success = describeAggregates(pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 0a181b01d9..d0904013d9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5611,3 +5611,360 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \dAp
+ * Describes access method properties.
+ *
+ * Takes an optional regexp to select particular access methods
+ */
+bool
+describeAccessMethodProperties(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ static const bool translate_columns[] = {false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ "SELECT a.amname AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_order') AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_unique') AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_multi_col') AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_exclude') AS \"%s\",\n"
+ :
+ "SELECT a.amname AS \"%s\",\n"
+ " a.amcanorder AS \"%s\",\n"
+ " a.amcanunique AS \"%s\",\n"
+ " a.amcanmulticol AS \"%s\",\n"
+ " a.amgettuple <> 0 AS \"%s\",\n",
+ gettext_noop("AM Name"),
+ gettext_noop("Can order"),
+ gettext_noop("Can unique"),
+ gettext_noop("Can multi col"),
+ gettext_noop("Can exclude"));
+
+ appendPQExpBuffer(&buf,
+ pset.sversion >= 110000
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_include') AS \"%s\""
+ : "FALSE AS \"%s\"",
+ gettext_noop("Can include"));
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_am a\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "amname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Access method properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAf
+ * Lists operator families associated with access method.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listAccessMethodOperatorFamilies(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " am.amname AS \"%s\",\n"
+ " ns.nspname AS \"%s\",\n"
+ " of.opfname AS \"%s\"\n",
+ gettext_noop("Access method"),
+ gettext_noop("Schema"),
+ gettext_noop("Name"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", usr.usename AS \"%s\"\n",
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ " FROM pg_catalog.pg_opfamily of\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n");
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_user usr ON of.opfowner = usr.usesysid\n");
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, "am.amname",
+ "am.amname", NULL, NULL);
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "ns.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of operator families");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+
+}
+
+/*
+ * \dAfo
+ * Lists operators associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listFamilyClassOperators(const char *access_method_pattern,
+ const char *family_pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, true, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " nsf.nspname AS \"%s\",\n"
+ " of.opfname AS \"%s\",\n"
+ " pg_catalog.format_type(o.amoplefttype, NULL) AS \"%s\",\n"
+ " pg_catalog.format_type(o.amoprighttype, NULL) AS \"%s\",\n"
+ " o.amopstrategy AS \"%s\",\n"
+ " CASE WHEN pg_catalog.pg_operator_is_visible(op.oid) \n"
+ " THEN op.oprname::pg_catalog.text \n"
+ " ELSE o.amopopr::pg_catalog.regoper::pg_catalog.text \n"
+ " END AS \"%s\",\n"
+ " CASE o.amoppurpose\n"
+ " WHEN 'o' THEN '%s'\n"
+ " WHEN 's' THEN '%s'\n"
+ " END AS \"%s\",\n"
+ " ofs.opfname AS \"%s\"\n"
+ "FROM pg_catalog.pg_amop o\n"
+ " LEFT JOIN pg_catalog.pg_operator op ON op.oid = o.amopopr\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
+ " LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n",
+ gettext_noop("AM"),
+ gettext_noop("Opfamily Schema"),
+ gettext_noop("Opfamily Name"),
+ gettext_noop("Left type"),
+ gettext_noop("Right type"),
+ gettext_noop("Strategy"),
+ gettext_noop("Operator"),
+ gettext_noop("ordering"),
+ gettext_noop("search"),
+ gettext_noop("Purpose"),
+ gettext_noop("Sort family"));
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "nsf.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4, 5, 6;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List operators of family related to access method");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAfp
+ * Lists procedures associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " ns.nspname AS \"%s\",\n"
+ " of.opfname AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amprocrighttype, NULL) AS \"%s\",\n"
+ " ap.amprocnum AS \"%s\",\n"
+ " ap.amproc::pg_catalog.regproc::pg_catalog.text ||\n"
+ " '(' || pg_catalog.pg_get_function_arguments(ap.amproc) || ')' AS \"%s\"\n"
+ "FROM pg_catalog.pg_amproc ap\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = ap.amprocfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n",
+ gettext_noop("AM"),
+ gettext_noop("Family schema"),
+ gettext_noop("Family name"),
+ gettext_noop("Left"),
+ gettext_noop("Right"),
+ gettext_noop("Number"),
+ gettext_noop("Proc name"));
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "ns.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf,
+ "ORDER BY 1, 2, 3, 4, 5, 6;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of operator family procedures");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAoc
+ * List index access method operator classes.
+ * Takes an optional regexp to select particular access method and operator class.
+ */
+bool
+describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *opclass_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT"
+ " am.amname AS \"%s\",\n"
+ " n.nspname AS \"%s\",\n"
+ " c.opcname AS \"%s\",\n",
+ gettext_noop("Access method"),
+ gettext_noop("Schema"),
+ gettext_noop("Name"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\",\n",
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ " (CASE WHEN pg_catalog.pg_opfamily_is_visible(of.oid) THEN '' ELSE ofn.nspname || '.' END) || of.opfname AS \"%s\",\n"
+ " c.opcintype::pg_catalog.regtype AS \"%s\",\n"
+ " c.opcdefault AS \"%s\",\n"
+ " (CASE WHEN c.opckeytype = 0 OR c.opckeytype = c.opcintype\n"
+ " THEN NULL\n"
+ " ELSE c.opckeytype\n"
+ " END)::pg_catalog.regtype AS \"%s\"\n"
+ "FROM pg_catalog.pg_opclass c\n"
+ " LEFT JOIN pg_catalog.pg_am am on am.oid = c.opcmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.opcnamespace\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = c.opcfamily\n"
+ " LEFT JOIN pg_catalog.pg_namespace ofn ON ofn.oid = of.opfnamespace\n",
+ gettext_noop("Family"),
+ gettext_noop("Indexed type"),
+ gettext_noop("Is default"),
+ gettext_noop("Stored type"));
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname", NULL, NULL);
+ if (opclass_pattern)
+ processSQLNamePattern(pset.db, &buf, opclass_pattern, have_where, false,
+ "n.nspname", "c.opcname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2,3;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index access method operator classes");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index a4cc5efae0..33ee81c485 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -111,4 +111,25 @@ bool describePublications(const char *pattern);
/* \dRs */
bool describeSubscriptions(const char *pattern, bool verbose);
+/* \dAf */
+extern bool listAccessMethodOperatorFamilies(const char *access_method_pattern,
+ const char *family_pattern,
+ bool verbose);
+
+/* \dAfp */
+extern bool listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern);
+
+/* \dAfo */
+extern bool listFamilyClassOperators(const char *accessMethod_pattern,
+ const char *family_pattern);
+
+/* \dAp */
+extern bool describeAccessMethodProperties(const char *pattern);
+
+/* \dAoc */
+extern bool describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *opclass_pattern,
+ bool verbose);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 586aebddd3..3d6de16f0b 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -224,6 +224,11 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\d[S+] NAME describe table, view, sequence, or index\n"));
fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n"));
fprintf(output, _(" \\dA[+] [PATTERN] list access methods\n"));
+ fprintf(output, _(" \\dAf[+] [AMPTRN [OPFPTRN]] list operator families of access method\n"));
+ fprintf(output, _(" \\dAfp [AMPTRN [OPFPTRN]] list procedures of operator family related to access method\n"));
+ fprintf(output, _(" \\dAfo [AMPTRN [OPFPTRN]] list operators of family related to access method\n"));
+ fprintf(output, _(" \\dAoc[+][AMPTRN [OPCPTRN]] list operator classes of index access methods\n"));
+ fprintf(output, _(" \\dAp [PATTERN] list access methods with properties\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n"));
fprintf(output, _(" \\dC[+] [PATTERN] list casts\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9dbd555166..6c1c6b918d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -460,6 +460,23 @@ static const SchemaQuery Query_for_list_of_relations = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_operator_families = {
+ /* min_server_version */
+ 0,
+ /* catname */
+ "pg_catalog.pg_opfamily c",
+ /* selcondition */
+ NULL,
+ /* viscondition */
+ "true",
+ /* namespace */
+ "c.opfnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.opfname)",
+ /* qualresult */
+ NULL
+};
+
/* Relations supporting INSERT, UPDATE or DELETE */
static const SchemaQuery Query_for_list_of_updatables = {
.catname = "pg_catalog.pg_class c",
@@ -1329,7 +1346,8 @@ psql_completion(const char *text, int start, int end)
"\\a",
"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
"\\copyright", "\\crosstabview",
- "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
+ "\\d", "\\da", "\\dA", "\\dAp", "\\dAf", "\\dAfo", "\\dAfp", "\\dAoc",
+ "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3818cfea7e..9d5344d392 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -3243,3 +3243,158 @@ last error message: division by zero
\echo 'last error code:' :LAST_ERROR_SQLSTATE
last error code: 22012
\unset FETCH_COUNT
+-- check printing info about access methods
+\dA
+List of access methods
+ Name | Type
+--------+-------
+ brin | Index
+ btree | Index
+ gin | Index
+ gist | Index
+ hash | Index
+ spgist | Index
+(6 rows)
+
+\dA gin
+List of access methods
+ Name | Type
+------+-------
+ gin | Index
+(1 row)
+
+\dA foo
+List of access methods
+ Name | Type
+------+------
+(0 rows)
+
+\dAp gin
+ Access method properties
+ AM Name | Can order | Can unique | Can multi col | Can exclude | Can include
+---------+-----------+------------+---------------+-------------+-------------
+ gin | f | f | t | f | f
+(1 row)
+
+\dAp foo
+ Access method properties
+ AM Name | Can order | Can unique | Can multi col | Can exclude | Can include
+---------+-----------+------------+---------------+-------------+-------------
+(0 rows)
+
+\dAf gin
+ List of operator families
+ Access method | Schema | Name
+---------------+------------+----------------
+ gin | pg_catalog | array_ops
+ gin | pg_catalog | tsvector_ops
+ gin | pg_catalog | jsonb_ops
+ gin | pg_catalog | jsonb_path_ops
+(4 rows)
+
+\dAf foo
+ List of operator families
+ Access method | Schema | Name
+---------------+--------+------
+(0 rows)
+
+\dAfo brin uuid_minmax_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Left type | Right type | Strategy | Operator | Purpose | Sort family
+------+-----------------+-----------------+-----------+------------+----------+----------+---------+-------------
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 1 | < | search |
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 2 | <= | search |
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 3 | = | search |
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 4 | >= | search |
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 5 | > | search |
+(5 rows)
+
+\dAfo brin bar
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Left type | Right type | Strategy | Operator | Purpose | Sort family
+----+-----------------+---------------+-----------+------------+----------+----------+---------+-------------
+(0 rows)
+
+\dAfo foo bar
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Left type | Right type | Strategy | Operator | Purpose | Sort family
+----+-----------------+---------------+-----------+------------+----------+----------+---------+-------------
+(0 rows)
+
+\dAfo * pg_catalog.jsonb_path_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Left type | Right type | Strategy | Operator | Purpose | Sort family
+-----+-----------------+----------------+-----------+------------+----------+----------+---------+-------------
+ gin | pg_catalog | jsonb_path_ops | jsonb | jsonb | 7 | @> | search |
+(1 row)
+
+\dAfp brin uuid_minmax_ops
+ List of operator family procedures
+ AM | Family schema | Family name | Left | Right | Number | Proc name
+------+---------------+-----------------+------+-------+--------+---------------------------------------------------------------
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 1 | brin_minmax_opcinfo(internal)
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 2 | brin_minmax_add_value(internal, internal, internal, internal)
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 3 | brin_minmax_consistent(internal, internal, internal)
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 4 | brin_minmax_union(internal, internal, internal)
+(4 rows)
+
+\dAfp brin bar
+ List of operator family procedures
+ AM | Family schema | Family name | Left | Right | Number | Proc name
+----+---------------+-------------+------+-------+--------+-----------
+(0 rows)
+
+\dAfp foo bar
+ List of operator family procedures
+ AM | Family schema | Family name | Left | Right | Number | Proc name
+----+---------------+-------------+------+-------+--------+-----------
+(0 rows)
+
+\dAfp * pg_catalog.uuid_ops
+ List of operator family procedures
+ AM | Family schema | Family name | Left | Right | Number | Proc name
+-------+---------------+-------------+------+-------+--------+----------------------------------
+ btree | pg_catalog | uuid_ops | uuid | uuid | 1 | uuid_cmp(uuid, uuid)
+ btree | pg_catalog | uuid_ops | uuid | uuid | 2 | uuid_sortsupport(internal)
+ hash | pg_catalog | uuid_ops | uuid | uuid | 1 | uuid_hash(uuid)
+ hash | pg_catalog | uuid_ops | uuid | uuid | 2 | uuid_hash_extended(uuid, bigint)
+(4 rows)
+
+\dAoc brin pg*.oid*
+ Index access method operator classes
+ Access method | Schema | Name | Family | Indexed type | Is default | Stored type
+---------------+------------+----------------+----------------+--------------+------------+-------------
+ brin | pg_catalog | oid_minmax_ops | oid_minmax_ops | oid | t |
+(1 row)
+
+\dA+
+ List of access methods
+ Name | Type | Handler | Description
+--------+-------+-------------+----------------------------------------
+ brin | Index | brinhandler | block range index (BRIN) access method
+ btree | Index | bthandler | b-tree index access method
+ gin | Index | ginhandler | GIN index access method
+ gist | Index | gisthandler | GiST index access method
+ hash | Index | hashhandler | hash index access method
+ spgist | Index | spghandler | SP-GiST index access method
+(6 rows)
+
+\dA+ gin
+ List of access methods
+ Name | Type | Handler | Description
+------+-------+------------+-------------------------
+ gin | Index | ginhandler | GIN index access method
+(1 row)
+
+\dA+ foo
+ List of access methods
+ Name | Type | Handler | Description
+------+------+---------+-------------
+(0 rows)
+
+\dAf+ foo
+ List of operator families
+ Access method | Schema | Name | Owner
+---------------+--------+------+-------
+(0 rows)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b45da9bb8d..ec5a673f92 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -688,3 +688,26 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\echo 'last error code:' :LAST_ERROR_SQLSTATE
\unset FETCH_COUNT
+
+-- check printing info about access methods
+\dA
+\dA gin
+\dA foo
+\dAp gin
+\dAp foo
+\dAf gin
+\dAf foo
+\dAfo brin uuid_minmax_ops
+\dAfo brin bar
+\dAfo foo bar
+\dAfo * pg_catalog.jsonb_path_ops
+\dAfp brin uuid_minmax_ops
+\dAfp brin bar
+\dAfp foo bar
+\dAfp * pg_catalog.uuid_ops
+\dAoc brin pg*.oid*
+
+\dA+
+\dA+ gin
+\dA+ foo
+\dAf+ foo
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index fcde01b2d4..e88c62c1c3 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1450,6 +1450,34 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dip [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+
+ <listitem>
+ <para>
+ Shows index properties listed in
+ <xref linkend="functions-info-index-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dicp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Shows index column properties listed in
+ <xref linkend="functions-info-index-column-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4d0f619186..d253089b40 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -805,6 +805,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'v':
case 'm':
case 'i':
+ if (strncmp(cmd, "dip", 3) == 0)
+ {
+ success = describeIndexProperties(pattern, show_system);
+ break;
+ }
+ else if (strncmp(cmd, "dicp", 4) == 0)
+ {
+ success = describeIndexColumnProperties(pattern, show_system);
+ break;
+ }
case 's':
case 'E':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d0904013d9..a06b7e0267 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -18,6 +18,7 @@
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
#include "catalog/pg_default_acl_d.h"
+#include "catalog/pg_index.h"
#include "fe_utils/string_utils.h"
#include "common.h"
@@ -44,6 +45,9 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
const char *pnspname, const char *prsname);
static void printACLColumn(PQExpBuffer buf, const char *colname);
static bool listOneExtensionContents(const char *extname, const char *oid);
+static bool describeOneIndexColumnProperties(const char *oid, const char *nspname,
+ const char *idxname, const char *amname,
+ const char *tabname);
/*----------------
@@ -5968,3 +5972,264 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern,
PQclear(res);
return true;
}
+
+/*
+ * \dip
+ * Describes index properties.
+ *
+ * Takes an optional regexp to select particular index.
+ */
+bool
+describeIndexProperties(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT"
+ " n.nspname AS \"%s\",\n"
+ " c.relname AS \"%s\",\n"
+ " am.amname AS \"%s\",\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Access method"));
+ appendPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ " pg_catalog.pg_index_has_property(c.oid, 'clusterable') AS \"%s\",\n"
+ " pg_catalog.pg_index_has_property(c.oid, 'index_scan') AS \"%s\",\n"
+ " pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan') AS \"%s\",\n"
+ " pg_catalog.pg_index_has_property(c.oid, 'backward_scan') AS \"%s\"\n"
+ :
+ " am.amclusterable AS \"%s\",\n"
+ " am.amgettuple <> 0 AS \"%s\",\n"
+ " am.amgetbitmap <> 0 AS \"%s\",\n"
+ " am.amcanbackward AS \"%s\"\n",
+ gettext_noop("Clusterable"),
+ gettext_noop("Index scan"),
+ gettext_noop("Bitmap scan"),
+ gettext_noop("Backward scan"));
+ appendPQExpBufferStr(&buf,
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"
+ "WHERE c.relkind='i'\n"
+ " AND n.nspname !~ 'pg_toast'\n");
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf,
+ " AND n.nspname <> 'pg_catalog'\n"
+ " AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ "n.nspname", "c.relname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dicp
+ * Describes index index column properties.
+ *
+ * Takes an optional regexp to select particular index.
+ */
+bool
+describeIndexColumnProperties(const char *index_pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ int i;
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT c.oid,\n"
+ " n.nspname,\n"
+ " c.relname,\n"
+ " am.amname,\n"
+ " c2.relname\n"
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\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\n");
+
+ appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n");
+
+ if (!showSystem && !index_pattern)
+ appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n"
+ "AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, index_pattern, true, false,
+ "n.nspname", "c.relname", NULL,
+ "pg_catalog.pg_table_is_visible(c.oid)");
+
+ appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0)
+ {
+ if (!pset.quiet)
+ {
+ if (index_pattern)
+ psql_error("Did not find any index named \"%s\"\n",
+ index_pattern);
+ else
+ psql_error("Did not find any relations.\n");
+ }
+ PQclear(res);
+ return false;
+ }
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ const char *oid = PQgetvalue(res, i, 0);
+ const char *nspname = PQgetvalue(res, i, 1);
+ const char *idxname = PQgetvalue(res, i, 2);
+ const char *amname = PQgetvalue(res, i, 3);
+ const char *tabname = PQgetvalue(res, i, 4);
+
+ if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname,
+ tabname))
+ {
+ PQclear(res);
+ return false;
+ }
+ if (cancel_pressed)
+ {
+ PQclear(res);
+ return false;
+ }
+ }
+
+ PQclear(res);
+ return true;
+}
+
+static bool
+describeOneIndexColumnProperties(const char *oid,
+ const char *nspname,
+ const char *idxname,
+ const char *amname,
+ const char *tabname)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ char *footers[3] = {NULL, NULL};
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " a.attname AS \"%s\",\n"
+ " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n"
+ " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n",
+ gettext_noop("Column name"),
+ gettext_noop("Expr"),
+ gettext_noop("Opclass"));
+
+ if (pset.sversion >= 90600)
+ appendPQExpBuffer(&buf,
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n"
+ " ELSE NULL"
+ " END AS \"%s\","
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n"
+ " ELSE NULL"
+ " END AS \"%s\","
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls') AS \"%s\"\n",
+ gettext_noop("ASC"),
+ gettext_noop("Nulls first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Returnable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+ else
+ appendPQExpBuffer(&buf,
+ " CASE WHEN am.amcanorder THEN (i.indoption[a.attnum - 1] & %d) = 0 ELSE NULL END AS \"%s\",\n" /* INDOPTION_DESC */
+ " CASE WHEN am.amcanorder THEN (i.indoption[a.attnum - 1] & %d) <> 0 ELSE NULL END AS \"%s\",\n" /* INDOPTION_NULLS_FIRST */
+ " am.amcanorder AS \"%s\",\n"
+ " am.amcanorderbyop AS \"%s\",\n"
+ " am.amsearcharray AS \"%s\",\n"
+ " am.amsearchnulls AS \"%s\"\n",
+ INDOPTION_DESC,
+ gettext_noop("ASC"),
+ INDOPTION_NULLS_FIRST,
+ gettext_noop("Nulls first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n");
+ if (pset.sversion < 90600)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n");
+ appendPQExpBuffer(&buf,
+ "WHERE c.oid = %s\n"
+ "ORDER BY a.attnum",
+ oid);
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+ if (PQntuples(res) == 0)
+ {
+ PQclear(res);
+ return true;
+ }
+
+ myopt.nullPrint = NULL;
+ myopt.title = psprintf(_("Index %s.%s"), nspname, idxname);
+ footers[0] = psprintf(_("Table: %s"), tabname);
+ footers[1] = psprintf(_("Access method: %s"), amname);
+ myopt.footers = footers;
+ myopt.topt.default_footer = false;
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 33ee81c485..057863ff02 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -132,4 +132,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter
const char *opclass_pattern,
bool verbose);
+/* \dip */
+extern bool describeIndexProperties(const char *pattern, bool showSystem);
+
+/* \dicp */
+extern bool describeIndexColumnProperties(const char *indexPattern,
+ bool showSystem);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 3d6de16f0b..04f824b91e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -247,6 +247,8 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n"));
+ fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6c1c6b918d..91fafebdae 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1349,8 +1349,8 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\dA", "\\dAp", "\\dAf", "\\dAfo", "\\dAfp", "\\dAoc",
"\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
- "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
- "\\dm", "\\dn", "\\do", "\\dO", "\\dp",
+ "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip",
+ "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", "\\dp",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
@@ -3432,6 +3432,10 @@ psql_completion(const char *text, int start, int end)
}
else if (TailMatchesCS("\\da*"))
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS("\\dAf*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
+ else if (TailMatchesCS("\\dAf*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, NULL);
else if (TailMatchesCS("\\dA*"))
COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
else if (TailMatchesCS("\\db*"))
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 3e61f50e7c..c3392d1d37 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1404,3 +1404,20 @@ insert into covidxpart values (4, 1);
insert into covidxpart values (4, 1);
ERROR: duplicate key value violates unique constraint "covidxpart4_a_b_idx"
DETAIL: Key (a)=(4) already exists.
+-- Test psql command for displaying information about indexes.
+\dip brinidx
+ Index properties
+ Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan
+--------+---------+---------------+-------------+------------+-------------+---------------
+ public | brinidx | brin | f | f | t | f
+(1 row)
+
+\dicp botharrayidx
+ Index public.botharrayidx
+ Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls
+-------------+------+-----------+-----+-------------+-----------+--------------------+------------+--------------+--------------
+ i | i | array_ops | | | f | f | f | f | f
+ t | t | array_ops | | | f | f | f | f | f
+Table: array_index_op_test
+Access method: gin
+
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 400b7eb7ba..bf875d982a 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -753,3 +753,7 @@ create unique index on covidxpart4 (a);
alter table covidxpart attach partition covidxpart4 for values in (4);
insert into covidxpart values (4, 1);
insert into covidxpart values (4, 1);
+
+-- Test psql command for displaying information about indexes.
+\dip brinidx
+\dicp botharrayidx
