Writing a psql script that exits on the first error

The default mode for the psql script tool is to continue processing when it finds an error. This sounds dumb, but it exists for historical compatibility only. There are some easy and mostly permanent ways to avoid this, so let's look at them.

Getting ready

Let's start with a simple script, with a command we know will fail:

$ $EDITOR test.sql
mistake1;
mistake2;
mistake3;

Execute the following script using psql to see what the results look like:

$ psql -f test.sql
psql:test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE 1: mistake1;
        ^
psql:test.sql:2: ERROR:  syntax error at or near "mistake2"
LINE 1: mistake2;
        ^
psql:test.sql:3: ERROR:  syntax error at or near "mistake3"
LINE 1: mistake3;
        ^

How to do it…

We will perform the following steps:

1. To exit the script on the first error, we can write the following command:

$ psql -f test.sql -v ON_ERROR_STOP=on
psql:test.sql:1: ERROR:  syntax error at or near "mistake1"
LINE...