Quickly Document Your Postgres Database Using psql Meta-Commands

Let's say you needed to document all of the tables in your PostgreSQL database. You wanted the output of the \d psql meta-command for all of the tables so you could put it in a shared documentation area. However, there were a lot of tables and you did not want to have to type all the commands that you needed. Before I explain how I can help, we will need to set up a simple database and provide some background on the \d psql meta-command.

Create a Test Database

Open a connection to a Postgres instance with the psql client tool. This example uses psql version 11.7 to use the postgres role to connect to a local postgres database.

psql -d postgres -U postgres

CREATE an empty database, using template0, just in case template1 has been modified.

CREATE DATABASE mctest TEMPLATE = template0;

Change the connection to the new empty database with the \c meta-command.

\c mctest

Make the foo table with a few constraints and sequence. We will use the serial data type to create the sequence and assign it to the f column.

CREATE TABLE foo (
    f serial NOT NULL
  , b integer
  , c integer
  , CONSTRAINT foo_pk PRIMARY KEY (f)
  , CONSTRAINT check_c_plus CHECK (c >0)
);

Make the bar table.

CREATE TABLE bar (
   b serial NOT NULL
 , d text
 , CONSTRAINT bar_pk PRIMARY KEY (b)
);

Create a relationship between foo and bar.

ALTER TABLE foo ADD CONSTRAINT foo_bar_fk FOREIGN KEY (b) REFERENCES bar(b);

Make a view that joins foo and bar.

CREATE VIEW foobar AS SELECT f, c, d FROM foo f JOIN bar b using (b);

Now we will add some comments to the relations.

COMMENT ON TABLE foo IS 'This is the table foo.';
COMMENT ON TABLE bar IS 'This is the table bar.';
COMMENT ON VIEW foobar IS 'View with combined rows from foo and bar';
COMMENT ON SEQUENCE foo_f_seq IS 'Sequence used to populate codes for foo.f';
COMMENT ON SEQUENCE bar_b_seq IS 'Sequence used to populate codes for bar.b';
COMMENT ON COLUMN foo.b IS 'This is a comment for column foo.b';

And we can add some data to the two tables using generate_series.

INSERT INTO bar (d) SELECT generate_series(0,1000);
INSERT INTO foo (b,c) SELECT b, generate_series(1,10) FROM bar

\d Meta-Command

Now that the mctest database and some relations have been created, we can work with the \d meta-command. Some meta-commands, like \d, are a shorthand way to get information about relations instead of writing complex queries against system tables:

 \d
          List of relations
 Schema |  Name     | Type     | Owner
--------+-----------+----------+----------
public  | bar       | table    | postgres
public  | bar_b_seq | sequence | postgres
public  | foo       | table    | postgres
public  | foo_f_seq | sequence | postgres
public  | foobar    | view     | postgres
(5 rows)

\d gave us the schema, names, types and owner for all the relations that we just created in mctest. In my case these objects were created in the public schema by the postgrees role, in your case the Schema and Owner may differ. Note that the relation list is sorted alphabetically not in the order we created the relations.

Appending a relation name to \d will get you further information about that relation.

When the relation is a table you will get information about the columns, indexes and references. For table bar, this is what we get:

\d bar
            Table "public.bar"
 Column | Type    | Collation | Nullable |Default
--------+---------+-----------+----------+--------------------------------
 b      | integer |           | not null | nextval('bar_b_seq'::regclass)
 d      | text    |           |          |
Indexes:
    "bar_pk" PRIMARY KEY, btree (b)
Referenced by:
    TABLE "foo" CONSTRAINT "foo_bar_fk" FOREIGN KEY (b) REFERENCES bar(b)

When the relation is a view, you will get information about the columns in the view. For the view foobar we get:

\d foobar
               View "public.foobar"
 Column | Type    | Collation | Nullable | Default
--------+---------+-----------+----------+---------
   f    | integer |           |          |
   c    | integer |           |          |
   d    | text    |           |          |

When the relation is a sequence, you will get information about that sequence. For the sequence bar_b_seq we get:

\d bar_b_seq
                Sequence "public.bar_b_seq"
 Type    | Start | Minimum | Maximum    | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |  1    | 1       | 2147483647 |  1        | no      | 1
Owned by: public.bar.b

But wait there’s more! More detailed information is available by appending a “+”. You can include information system relations by appending an “S”. The full format is \d[S+] [name].

\d+
                  List of relations
 Schema |  Name     | Type     | Owner    | Size       | Description

--------+-----------+----------+----------+------------+---------------------------
----------------
public  | bar       | table    | postgres | 72 kB      | This is the table bar.
...

With the addition of the “+” there are two new columns, Size and Description, but the result is too wide to fit on the page and the lines wrap around. To partially address this, use the meta-command \x on to switch the display to expanded output. Although long descriptions will appear on multiple lines, it will be easier to read.

\x auto
\d+
-[ RECORD 1 ]------------------------------------------
Schema      | public
Name        | bar
Type        | table
Owner       | postgres
Size        | 72 kB
Description | This is the table bar.
...
-[ RECORD 5 ]------------------------------------------
Schema      | public
Name        | foobar
Type        | view
Owner       | postgres
Size        | 0 bytes
Description | View with combined rows from foo and bar

\dS
                     List of relations
  Schema    |              Name               | Type     | Owner
------------+---------------------------------+----------+----------
pg_catalog  | pg_aggregate                    | table    | postgres
pg_catalog  | pg_am                           | table    | postgres
...
pg_catalog  | pg_views                        | view     | postgres
public      | bar                             | table    | postgres
public      | bar_b_seq                       | sequence | postgres
public      | foo                             | table    | postgres
public      | foo_f_seq                       | sequence | postgres
public      | foobar                          | view     | postgres

One last item before we get to our original question, the \d meta-command also supports wildcards.

\d bar*
                             Table "public.bar"
Column  | Type    | Collation | Nullable |        Default
--------+---------+-----------+----------+------------------ --------------
b.      | integer |           | not null | nextval('bar_b_seq'::regclass)
d       | text    |           |          |
Indexes:
    "bar_pk" PRIMARY KEY, btree (b)
Referenced by:
    TABLE "foo" CONSTRAINT "foo_bar_fk" FOREIGN KEY (b) REFERENCES bar(b)

                     Sequence "public.bar_b_seq"
 Type    | Start | Minimum | Maximum    | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |  1    |  1      | 2147483647 |  1        | no      | 1
Owned by: public.bar.b

       Index "public.bar_pk"
 Column | Type    | Key? | Definition
--------+---------+------+------------ ;
 b      | integer | yes  | b
primary key, btree, for table "public.bar"

There are quite a bit more meta-commands to learn about but let’s get back to the original problem. You wanted to get the \d output of every table so you could put it in a shared documentation area. The solution I gave provides flexibility to decide on which tables will be documented. There are times when you might not want to show every table in documentation; a table design might not be finished, perhaps the documentation is specific to a subset of tables. Kevin Walker, a co-worker of mine provides a much shorter way to do this using wildcards, which is included in this post as well.

PostgreSQL has a system view called pg_tables that lists all of the user and system tables in a database. Feel free to use \d pg_tables to see the structure of the view. If you wrote a SELECT query that would list all of the database's tables, you could concatenate the \d command with the tablename to create the list of meta-commands you needed to produce this documentation.

Step 1.

Write a query using pg_tables to get a list of the tables that were needed, but concatenate the \d command with the tablename. For the purpose of this post we will assume that this is for every table in the public schema, but this WHERE clause can be customized to fit your needs.

SELECT '\d ' || tablename FROM pg_tables WHERE schemaname = 'public';
 ?column?
----------
 \d bar
 \d foo
(2 rows)

Step 2.

Copy the \d meta-commands from the output above and paste into psql

 \d bar
                       Table "public.bar"
 Column |Type     | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------
  b     | integer |           | not null | nextval('bar_b_seq'::regclass)
  d     | text    |           |          |
Indexes:
    "bar_pk" PRIMARY KEY, btree (b)
Referenced by:
    TABLE "foo" CONSTRAINT "foo_bar_fk" FOREIGN KEY (b) REFERENCES bar(b)
\d foo
                       Table "public.foo"
 Column | Type    | Collation | Nullable |  Default
--------+---------+-----------+----------+--------------------------------
  f     | integer |           | not null | nextval('foo_f_seq'::regclass)
  b     | integer |           |          |
        | integer |           |          |
Indexes:
   "foo_pk" PRIMARY KEY, btree (f)
Check constraints:
   "check_c_plus" CHECK (c 0)
Foreign-key constraints:
   "foo_bar_fk" FOREIGN KEY (b) REFERENCES bar(b)

While this works, there are multiple steps involved. Software and database engineers strive to minimize manual steps to reduce errors and automate tedious tasks. For unix based systems, the following one line command does steps one and two above, plus a little more. The date is output, then output from the first psql command is piped, or passed, to the second psql command and that output is piped to grep to strip out the meta-command prompts.

"
date; psql -d mctest -U postgres -Atq  \

    -c " select '\d ' || tablename from pg_tables \

           where schemaname = 'public' order by 1 asc;" \

| psql -U postgres -d mctest | grep -v “mctest=#”

"

Tue Apr 7 05:25:09 UTC 2020
                         Table "public.bar"
 Column | Type    | Collation | Nullable |Default
--------+---------+-----------+----------+--------------------------------
  b     | integer |           | not null | nextval('bar_b_seq'::regclass)
  d     | text    |           |          |
Indexes:
    "bar_pk" PRIMARY KEY, btree (b)
Referenced by:
    TABLE "foo" CONSTRAINT "foo_bar_fk" FOREIGN KEY (b) REFERENCES bar(b)

                 Table "public.foo"
 Column |Type     | Collation | Nullable |Default
--------+---------+-----------+----------+--------------------------------
  f     | integer |           | not null | nextval('foo_f_seq'::regclass)
  b     | integer |           |          |
  c     | integer |           |          |
Indexes:
    "foo_pk" PRIMARY KEY, btree (f)
Check constraints:
    "check_c_plus" CHECK (c  0)
Foreign-key constraints:
"foo_bar_fk" FOREIGN KEY (b) REFERENCES bar(b)

The wildcard methods provided by my colleague are much shorter. The first provides detailed information for all objects in a specific schema. The second provides detailed information for all of the objects in every schema in the database.

psql -d <db-name> -c '\d+ <schema-name>.*' > output.txt

psql -d <db-name> -c '\d+ *.*' > output.txt

For this example, the commands would be:

psql -d mctest -c '\d+ public.*' > output.txt

psql -d mctest -c '\d+ *.*' > output.txt

There are quite a few more meta-commands. Use the \? within the psql client or check out the PostgreSQL documentation to get more information. Thank you to everyone involved with the development and maintenance of the psql client.