PostgreSQL 11 Administration Cookbook

How to do it…

Our task is to run a SQL statement using this form, with X as the table name, against each of our three test tables:

ALTER TABLE X ADD COLUMN last_update_timestamp TIMESTAMP WITH TIME ZONE;

The steps are as follows:

1. Our starting point is a script that lists the tables that we want to perform tasks against—something like the following:

        postgres=# SELECT relname               FROM pg_class c               JOIN pg_namespace n                 ON c.relnamespace = n.oid               WHERE n.nspname = 'test'                 AND c.relkind = 'r';

2. This displays the list of tables that we will act upon (so that you can check it):

         relname        ---------         a         b         c        (3 rows)

3. We then use the preceding SQL to generate the text for a SQL script, substituting the schema name and table name in the SQL text. We then ...