psql – Exit on First Error

Postgres has an awesome command line client we all use and love called psql. We usually use psql in an interactive mode where we connect to the database and then type and execute queries. But sometimes it is useful to execute a whole SQL file. We can do this with:

psql -U username -d database -f file.sql

The problem with executing an sql file like this is that psql will exit with a status code 0 (which means everything is OK), even when there are errors while executing file.sql. It would also ignore all errors and continue execution until the end of file, which is not what we would like every time. Because of this you cannot do simple checks in your scripts for the exit status with $? to know if the file was executed successfully. To resolve this, you could pass stdout and stderr to grep and search for ERROR: like this:

psql -U username -d database -f file.sql 2>&1 | grep "ERROR:"

This would work, but it’s not a very clean solution and you also don’t see the output of psql (like notices, etc). There is a simple and effective solution to this problem by using a variable ON_ERROR_STOP. If we set this variable to 1 or true, psql would stop on the first error and exit with status code 3, which makes it perfect for scripts. You can set this variable at the top of the sql file:

\set ON_ERROR_STOP true

or even better directly on the psql command line with parameter -v:

psql -v ON_ERROR_STOP=1 -U username -d database -f file.sql

Now we can easily check if the sql file was executed successfully like this:

psql -v ON_ERROR_STOP=1 -U username -d database -f file.sql
if [ $? -eq 0 ]; then
    echo Success
else
    echo Failed
fi