2

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?

4

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.

| improve this answer | |
1

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:/'
| improve this answer | |
0

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 "/, /^$/'
    
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.