Thread: psql \d for wide tables / pattern for individual columns

We have some very wide tables (historically, up to 1600 columns ; this is
improved now, but sometimes still several hundred, with numerous pages output
to psql pager).  Is is reasonable to suggest adding a psql command to show a
table's definition, without all the columns listed?

Or limit display to matching columns ?  That's more general than the above
functionality, if "empty string" is taken to mean "show no columns", like \d
table "" or \d table *id or \d table ????

Attached minimal patch for the latter.

postgres=# \d pg_attribute ""
        Table "pg_catalog.pg_attribute"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

postgres=# \d pg_attribute "attn*|attrel*"
            Table "pg_catalog.pg_attribute"
   Column   |   Type   | Collation | Nullable | Default 
------------+----------+-----------+----------+---------
 attrelid   | oid      |           | not null | 
 attname    | name     |           | not null | 
 attnum     | smallint |           | not null | 
 attndims   | integer  |           | not null | 
 attnotnull | boolean  |           | not null | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

postgres=# \d pg_attribute ??????
           Table "pg_catalog.pg_attribute"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 attlen | smallint  |           | not null | 
 attnum | smallint  |           | not null | 
 attacl | aclitem[] |           |          | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

postgres=# \d pg_attribute *id
         Table "pg_catalog.pg_attribute"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 attrelid | oid  |           | not null | 
 atttypid | oid  |           | not null | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

Em dom., 10 de nov. de 2019 às 18:29, Justin Pryzby
<pryzby@telsasoft.com> escreveu:
>
> We have some very wide tables (historically, up to 1600 columns ; this is
> improved now, but sometimes still several hundred, with numerous pages output
> to psql pager).  Is is reasonable to suggest adding a psql command to show a
> table's definition, without all the columns listed?
>
It seems a good idea. However, I'm afraid adding a second argument
could limit our capabilities to match/suppress other table properties
in the future. For example, I think psql might have a way to omit
indexes, FKs, partitions, some column properties, or even show GRANTs
for that table. I don't have a concrete plan at the moment but maybe
someone else already thought about it.

> Or limit display to matching columns ?  That's more general than the above
> functionality, if "empty string" is taken to mean "show no columns", like \d
> table "" or \d table *id or \d table ????
>
> Attached minimal patch for the latter.
>
> postgres=# \d pg_attribute ""
>         Table "pg_catalog.pg_attribute"
>  Column | Type | Collation | Nullable | Default
> --------+------+-----------+----------+---------
> Indexes:
>     "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
>     "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
>
The problem with your proposal is that I can't differentiate a
complete output from another suppress-some-columns output if you don't
provide the meta-command. I think you should explicitly show that some
columns were suppressed (something like "... suppressed columns..."
after the list of matched columns). If you don't, it could lead to
confusion while reporting table description.


--
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Euler Taveira <euler@timbira.com.br> writes:
> Em dom., 10 de nov. de 2019 às 18:29, Justin Pryzby
> <pryzby@telsasoft.com> escreveu:
>> We have some very wide tables (historically, up to 1600 columns ; this is
>> improved now, but sometimes still several hundred, with numerous pages output
>> to psql pager).  Is is reasonable to suggest adding a psql command to show a
>> table's definition, without all the columns listed?

> It seems a good idea. However, I'm afraid adding a second argument
> could limit our capabilities to match/suppress other table properties
> in the future.

Yeah, that was my immediate reaction to the proposed syntax as well.
I think we'd better make sure that we aren't foreclosing future
extensions of \d.

Maybe a reasonable idea is to expect that any additional arguments
are in "keyword=value" style, so that the immediate need could be
met with

\d mytable columns=<pattern>

It might already be worthwhile to allow both positive and negative
patterns, so also

\d mytable exclude_columns=<pattern>

> The problem with your proposal is that I can't differentiate a
> complete output from another suppress-some-columns output if you don't
> provide the meta-command. I think you should explicitly show that some
> columns were suppressed (something like "... suppressed columns..."
> after the list of matched columns). If you don't, it could lead to
> confusion while reporting table description.

Hm ... "N columns suppressed" might sometimes be useful, but I'm afraid
it would take an extra query to get it, and I'm not sure it's worth it.
I think someone who's using these options would already know perfectly
well what they're hiding.  We don't expect, say, "\dt my*" to tell you
how many tables it didn't list.

            regards, tom lane