Database Administration - PostgreSQL 9 Administration - page 157

Note how we use the window function row_number() to sort the data by size, then we split
the data into pieces using the following:
WHERE row_number % N = i;
N is the total number of scripts we're producing, and i is the number of the current script. The
numbering starts at zero because we are using modulo arithmetic to distribute the subtasks.
Using pg_batch to run tasks in parallel
There is a tool for running tasks in parallel, available at the following URL:
http://reorg.projects.postgresql.org/pg_batch.html
pg_batch runs tasks in the order it finds them, and splits them up blindly across multiple
parallel sessions. That means that you'll need to write a script to pre-order the items that
need to be executed, so that the tasks are distributed evenly across sessions, so you'll end up
writing something that looks exactly like the preceding scripts anyway.
Adding/Removing the columns of a table
As designs change, we may want to add or remove columns from our data tables. These are
common operations in development, though they need more careful planning on a running
production database server, as these operations take full locks and may run for long periods.
How to do it...
You can add a new column to a table using the following:
ALTER TABLE mytable
ADD COLUMN last_update_timestamp TIMESTAMP WITHOUT TIME ZONE;
Or drop the same column using the following command:
ALTER TABLE mytable
DROP COLUMN last_update_timestamp;
You can combine multiple operations when using ALTER TABLE , which then applies
the changes in sequence. This allows you to do a useful trick, which is to add a column
unconditionally, using IF EXISTS , such as the following:
ALTER TABLE mytable
DROP COLUMN IF EXISTS last_update_timestamp,
ADD COLUMN last_update_timestamp TIMESTAMP WITHOUT TIME ZONE;
Note that this will have almost the same effect as:
UPDATE mytable SET last_update_timestamp = NULL;