How to continue sql script on error?

Question

We have a couple of migration scripts, which alter the schema from version to version.

Sometimes it happens, that a migration step (e.g. adding a column to a table) was already done manually or by a patch installation, and thus the migration script fails.

How do I prevent the script from stopping on error (ideally at specific expected errors) and instead log a message and continue with the script?

We use PostgresQL 9.1, both a solution for PostgresQL as well as a general SQL solution would be fine.

Answer 1

Although @LucM's answer seems to be good recommendation - @TomasGreif pointed me to an answer that went more in the direction of my origin request.

For the given example of adding a column, this can be done by using the DO statement for catching the exception:

DO $$ 
    BEGIN
        BEGIN
            ALTER TABLE mytable ADD COLUMN counter integer default 0; 
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'counter column already exists';
        END;
    END;
$$;

The hint led me to the right PostgresQL site, describing the error codes one could trap - so that was the right solution for me.

Answer 2

I don't think that you have another solution than running the entire script outside of a transaction.

What I would do if I was in that situation:

• Do the modifications to the metadata (drop/create table/column...) outside of a transaction.
• Do all modifications to the data (update/insert/delete) inside a transaction.