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.
