How can I stop a Postgres script when it encounters an error?

Question

Is there a way to specify that when executing a sql script it stops when encountering the first error on the script, it usually continues, regardless of previous errors.

Answer 1

I think the solution to add following to .psqlrc is far from perfection

\set ON_ERROR_STOP on 

there exists much more simple and convenient way - use psql with parameter:

psql -v ON_ERROR_STOP=1 

better to use also -X parameter turning off .psqlrc file usage. Works perfectly for me

p.s. the solution found in great post from Peter Eisentraut. Thank you, Peter! http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html

Answer 2

I assume you are using psql, this might be handy to add to your ~/.psqlrc file.

\set ON_ERROR_STOP on 

This will make it abort on the first error. If you don't have it, even with a transaction it will keep executing your script but fail on everything until the end of your script.

And you probably want to use a transaction as Paul said. Which also can be done with psql --single-transaction ... if you don't want to alter the script.

So a complete example, with ON_ERROR_STOP in your .psqlrc:

psql --single-transaction --file /your/script.sql