Adding/removing columns on 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 they take full locks and may run for long periods.

How to do it…

You can add a new column to a table using this command:

ALTER TABLE mytable ADD COLUMN last_update_timestamp TIMESTAMP WITHOUT TIME ZONE;

You can 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 a sequence. This allows you to do a useful trick, which is to add a column unconditionally, using IF EXISTS, like this:

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 the following command:

UPDATE mytable SET last_update_timestamp...