Only describing postgres tables with "\d" (not sequences, indexes, views, ...)

Question

I want to describe every table in a postgres database.

Currently I have this:

psql -d db -c "\d *" > db_description.txt

But this also includes all sequences, views, etc.

Is there a command where the * pattern only matches tables?
Or is there another way to do this?

Answer 1

How about simply using the \dt command? It's here to list tables only. You can get more help on PostgreSQL inner commands by issuing the \? command.

If you want to know what those commands do behind the scenes, you could also start psql with the -E option, as asked here. You could therefore rewrite this query to get exactly what you want, be it views, sequences, tables... or any combination of the above.

Answer 2

You can massage the output of that until you strip everything you don't want:

psql -d db -c '\d *' |\
  sed -n '/^ *Table/,/^\([^T -]\|$\)/p' |\
  grep -v '^\([^T ]\|Tablesp\| *Col\|$\)' |\
  sed 's/^ *Table "\([^"]*\)"/\n\1:/'

Answer 3

Describe ONLY Tables Using awk Filter

I will iteratively demonstrate how to do this from within the PostgreSQL interactive terminal (psql). It can be adapted to run as a shell command using the -c option.

1. Pipe output—of the next command—to an awk filter to print only table definitions.

   pachl=# \o |awk '/^ *Table "/, /^$/'
   

   This awk command prints all lines between start/stop pairs (e.g. /start/, /stop/). This filter expression is fairly simple because PostgreSQL delimits each object with a common header (e.g. Table "schema.entity") and an empty line.

   Or, if you want definitions to go directly into a file for later reference, you can instead do:

   pachl=# \o |awk '/^ *Table "/, /^$/' >/tmp/tbldef
   

2. Describe all tables, views, sequences, and indices in a schema.

   pachl=# \d YOUR-SCHEMA.*
   

   The awk filter will only print table definitions. Specifying a schema name is probably what you want. Without a qualifying schema the wildcard (*) will include PostgreSQL catalog objects, public, etc.

3. Reset output when finished.

   pachl=# \o
   

   This will remove the awk filter pipe.

Describe ONLY Indices, Sequences, or Views

This awk filter can modified to describe other types of objects too.

• Indices

  pachl=# \o |awk '/^ *Index "/, /^$/'
  

• Sequences

  pachl=# \o |awk '/^ *Sequence "/, /^$/'
  

• Views

  pachl=# \o |awk '/^ *View "/, /^$/'