Postgresql Psql Stop On Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company psql on_error_rollback Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs psql continue on error Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, psql command line just like you, helping each other. Join them; it only takes a minute: Sign up Postgres sql fail on script error up vote 44 down vote favorite 6 Is there a way to specify that when executing psql commands a sql script it stops when encountering the first error on the script, it usually continues, regardless of previous errors. Thanks postgresql share|improve this question asked Dec 18 '10 at 21:54 Ray 221133 add a comment| 3 Answers 3 active oldest votes up vote 67 down vote 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
On_error_stop=1
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 share|improve this answer edited Jun 18 '13 at 12:42 answered Apr 26 '13 at 15:08 Alfishe 1,040911 3 -v ON_ERROR_STOP=ON also works, at least with 9.2. I suspect any of the variants of boolean "true" are allowed. –jpmc26 Feb 14 '14 at 11:38 It doesn't work in interactive mode, which confused me for a minute. –Sam Watkins Jul 30 '15 at 5:47 add a comment| up vote 5 down vote 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 share|improve this answer answered Dec 18 '10 at 23:40 plundra 8,52831721 2 Even if the transaction fails, the exi
8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.2.18 Documentation Prev Up Next psql Namepsql-- PostgreSQL interactive terminal Synopsis psql [option...] [dbname [username]] Description psql is a terminal-based front-end to
Man Psql
PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see psql run sql file the query results. Alternatively, input can be from a file. In addition, it provides a number of meta-commands and various shell-like features to psql exit facilitate writing scripts and automating a wide variety of tasks. Options -a --echo-all Print all nonempty input lines to standard output as they are read. (This does not apply to lines read interactively.) This is equivalent to setting http://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error the variable ECHO to all. -A --no-align Switches to unaligned output mode. (The default output mode is otherwise aligned.) -c command --command=command Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option. command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash https://www.postgresql.org/docs/9.2/static/app-psql.html command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, for example: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.) If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard input. Also, only the result of the last SQL command is returned. Because of these legacy behaviors, putting more than one command in the -c string often has unexpected results. It's better to feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example: psql < log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta http://dba.stackexchange.com/questions/24518/how-to-conditionally-stop-a-psql-script-based-on-a-variable-value Discuss the workings and policies of this site About Us Learn https://bytes.com/topic/postgresql/answers/422601-psql-immediately-exit-after-error more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database on error skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How to conditionally stop a psql script (based on a variable value)? up vote 5 down vote favorite postgresql psql stop Let's consider the following example (from the start of a psql script): \c :db_to_run_on TRUNCATE the_most_important_table; -- tried to avoid similarities to anything that exists out there Now if it is run this by the command psql [connection details] -v db_to_run_on=\'dev_database\' then it just runs and the user is happy. But what if (s)he decides to specify -v db_to_run_on=production_database? (Let's assume that this can happen, just like people run rm -rf / # don't try this at home!!! ocassionally.) Hopefully there is a fresh backup of that table... So the question arises: how to check the variables passed to a script and stop further processing based on their value? postgresql psql share|improve this question edited Sep 19 '12 at 8:51 asked Sep 19 '12 at 8:32 dezso 14.3k73563 psql is quite limited as a batch tool, as it lacks control structures. I'd recommend writing scripts for this sort of thing in perl/python/whatever using the appropriate database driver. –Craig Ringer Sep 19 '12 at 10:59 @CraigRinger it is, un help? Post your question and get tips & solutions from a community of 418,619 IT Pros & Developers. It's quick & easy. psql: immediately exit after an error? P: n/a David Garamond Can psql be told to exit immediately after an error (especially when doing commands from a file, -f)? This is the default behaviour of the mysql client, except when we give it -f option ("force"). The problem is, when restoring a dump, a failure at the some point might cause the subsequent commands to produce wrong results (e.g. I redefine a builtin function with a plruby function with different behaviour, but plruby failed to be installed due to wrong path. Thus the subsequent commands are executed using the builtin function which is not the expected one.) Furthermore, you can't check on psql exit code to see whether _any_ command was not successfully executed. Of course one should examine the full psql output after a restore anyway, and the option to exit immediately after an error can save time (especially for large dumps). -- dave ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html Nov 23 '05 #1 Post Reply Share this Question 4 Replies P: n/a Mike G I would think that depends upon how the sql in the file is coded. You can use the RAISE NOTICE / ERROR commands to abort a function's execution. Can psql be told to exit immediately after an error (especially when doing commands from a file, -f)? This is the default behaviour of the mysql client, except when we give it -f option ("force"). The problem is, when restoring a dump, a failure at the some point might cause the subsequent commands to produce wrong results (e.g. I redefine a builtin function with a plruby function with different behaviour, but plruby failed to be installed due to wrong path. Thus the subsequent commands are executed using the builtin function which is not the expected one.) Furthermore, you can't check on psql exit code to see whether _any_ command was not succes