How to 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

Answer 3

It's not exactly what you want, but if you start your script with begin transaction; and end with end transaction;, it will actually skip everything after the first error, and then it will rollback everything it did before the error.

Answer 4

I always like to reference the manual directly.

From the PostgreSQL Manual:

Exit Status

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

By default if the sql code you are running on the PostgreSQL server error psql won't quit an error. It will catch the error and continue. If, as mentioned above, you set the ON_ERROR_STOP setting to on, when psql catches an error in the sql code it will exit and return 3 to the shell.

Answer 5

For example, you can enable ON_ERROR_STOP which is off by default when you log in with the user john and the database apple as shown below. *ON_ERROR_STOP is off automatically after logout:

psql -U john -v ON_ERROR_STOP=on apple

psql -U john --variable=ON_ERROR_STOP=on apple

psql -U john --set=ON_ERROR_STOP=on apple 

And, you can enable ON_ERROR_STOP when you run a script from the file test.sql as shown below:

psql -U john -v ON_ERROR_STOP=on -f test.sql apple

And, you can enable ON_ERROR_STOP after login as shown below:

\set ON_ERROR_STOP on

Or. *This command can only enable ON_ERROR_STOP without toggling on and off:

\set ON_ERROR_STOP

And, you can show the value of ON_ERROR_STOP with \echo as shown below. *Don't forget to put : just before ON_ERROR_STOP:

postgres=# \echo :ON_ERROR_STOP
on

*Memos:

• Don't use the lowercase on_error_stop which doesn't work.

• You can enable ON_ERROR_STOP with oN, 1, TrUe, tR, etc.

• You can disable ON_ERROR_STOP with oFf, 0, FaLsE, fA, etc.

• It happens that error doesn't stop execution when you run a script from a file.

• RAISE statement with DEBUG, LOG, INFO, NOTICE or WARNING cannot stop execution even if ON_ERROR_STOP is on while RAISE statement with EXCEPTION or nothing can. *My question explains it in detail.