139

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.

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
Ray
  • 1,391
  • 2
  • 8
  • 3

4 Answers4

214

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

exhuma
  • 20,071
  • 12
  • 90
  • 123
Alfishe
  • 3,430
  • 1
  • 25
  • 19
  • 10
    `-v ON_ERROR_STOP=ON` also works, at least with 9.2. I suspect any of the [variants of boolean "true"](http://www.postgresql.org/docs/9.2/static/datatype-boolean.html) are allowed. – jpmc26 Feb 14 '14 at 11:38
  • 2
    It doesn't work in interactive mode, which confused me for a minute. – Sam Watkins Jul 30 '15 at 05:47
  • Without "on" explicitly set, it will toggle. This bit me when I had a script working great and then broke it into several scripts that all ran the same initialization script. Then, the subscripts were toggling this parameter on and off. :( – combinatorist Feb 16 '22 at 17:28
33

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
plundra
  • 18,542
  • 3
  • 33
  • 27
9

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.

Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
  • True, but, it still parses everything. And if you want to do a *second* transaction only if the first succeeded, this won't work. – Wildcard May 06 '17 at 04:22
  • Yes, and not to forget continuing when it encounters DDL Create table errrors... (version: postrgres 10). Yes, it skips one table and goes onto the others... – JL Peyret Jan 23 '19 at 09:30
1

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.

Gregory Arenius
  • 2,904
  • 5
  • 26
  • 47