Postgres sql error on script error

Question

Is there any way to indicate that when executing the sql script it stops when the first error occurs in the script, it usually continues, regardless of previous errors.

thank

Answer 1

I think the decision to add the following to .psqlrc is far from perfect

\set ON_ERROR_STOP on 

There is a much simpler and more convenient way - use psql with the parameter:

 psql -v ON_ERROR_STOP=1 

it is better to use the -X option, disabling the use of the .psqlrc file. Works great for me

ps solution found in a great post from Peter Eisentrout. Thanks Peter! http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html

Answer 2

I assume you are using psql , it may be convenient to add ~/.psqlrc to your file.

 \set ON_ERROR_STOP on 

This will interrupt the first error. If you do not have it, even with a transaction, it will continue to execute your script, but will not work until the end of your script.

And you probably want to use a transaction, as Paul said. What can also be done with psql --single-transaction ... if you do not want to modify the script.

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

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

Answer 3

This is not exactly what you want, but if you start your script with begin transaction; and end with end transaction; , he will actually skip everything after the first error, and then discard everything he did before the error.