Postgres Psql Stop On Error
Contents |
use psql in an interactive mode where we connect to the database and then type and execute queries. But sometimes it is useful to execute a
Psql Set On_error_stop
whole SQL file. We can do this with: psql -U username -d database psql on_error_rollback -f file.sql The problem with executing an sql file like this is that psql will exit with a status code 0 psql continue on error (which means everything is OK), even when there are errors while executing file.sql. It would also ignore all errors and continue execution until the end of file, which is not what we would
Psql Command Line
like every time. Because of this you cannot do simple checks in your scripts for the exit status with $? to know if the file was executed successfully. To resolve this, you could pass stdout and stderr to grep and search for ERROR: like this: psql -U username -d database -f file.sql 2>&1 | grep "ERROR:" This would work, but it's not a very clean solution and
Psql Commands
you also don't see the output of psql (like notices, etc). There is a simple and effective solution to this problem by using a variable ON_ERROR_STOP. If we set this variable to 1 or true, psql would stop on the first error and exit with status code 3, which makes it perfect for scripts. You can set this variable at the top of the sql file: \set ON_ERROR_STOP true or even better directly on the psql command line with parameter -v: psql -v ON_ERROR_STOP=1 -U username -d database -f file.sql Now we can easily check if the sql file was executed successfully like this: psql -v ON_ERROR_STOP=1 -U username -d database -f file.sql if [ $? -eq 0 ]; then echo Success else echo Failed fi I hope this short tip comes in handy. "psql - Exit on First Error" has been shared times on Facebook. Share it on Facebook too! "psql - Exit on First Error" has been shared 3 times on Twitter. Share it on Twitter too! "psql - Exit on First Error" has been shared times on Google+. Share it on Google+ too! "psql - Exit on First Error" has been shared 31 times on LinkedIn. Shar
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 on_error_stop=1 is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively,
Man Psql
issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file. In addition, it provides a psql run sql file number of meta-commands and various shell-like features to 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 http://blog.nordeus.com/dev-ops/psql-exit-on-first-error.htm not apply to lines read interactively.) This is equivalent to setting 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 https://www.postgresql.org/docs/9.2/static/app-psql.html is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash 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 Discuss the workings and policies of this site About Us Learn http://dba.stackexchange.com/questions/24518/how-to-conditionally-stop-a-psql-script-based-on-a-variable-value 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 https://andreas.scherbaum.la/blog/archives/251-stop-psql-on-first-error.html answer site for database professionals who wish to improve their database 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 on error 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 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 postgres psql stop 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, unfortunately. I have an idea now, but it needs a bit of honing yet. –dezso Sep 19 '12 at 11:13 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP. If we could raise an error somehow, this would do what we want. The problem is that we have to test psql will stop right after the first error and will not try to continue any other commands in the chain. Posted by Andreas 'ads' Scherbaum in Code Snippets, PostgreSQL News at 00:27 | Comments (0) | Trackbacks (0) Random Entry: Wöchentlicher PostgreSQL Newsletter - 21. September 2008< Previous posting | Next posting >Defined tags for this entry: Code snippets, PostgreSQL, psqlRelated entries by tags: First PostgreSQL Meetup in Hamburg March South Bay PostgreSQL Meetup at Adobe PgConf.Russia 2016 FOSDEM PGDay 2016 Schedule FOSDEM PGDay and Devroom 2016 - Announcement & Call for Papers Trackbacks Trackback specific URI for this entry No Trackbacks Comments Display comments as (Linear | Threaded) No comments Add Comment Name Email Homepage In reply to [ Top level ] Comment Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.E-Mail addresses will not be displayed and will only be used for E-Mail notifications.To leave a comment you must approve it via e-mail, which will be sent to your address after submission. Remember Information? Quicksearch Blog Administration Open login screen PostgreSQL Buch PostgreSQL für Anwender, Administratoren und Entwickler Webseite zum Buch Beim Verlag bestellen Calendar October '16 Mon Tue Wed Thu Fri Sat Sun 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Bookmarks Bookmarks About me My company Ea my projects Writings Pictures PGUG.de Friends A. Kretschmer A. Kretschmer Blog Stephan Jaensch wuffel formorer mastermind BC-bd Tobias Scherbaum David Fetter Guillaume Lelarge Blogroll Hostblogger BILDblog Woxikon Blog Upcoming Events Categories / Databases Greenplum other DBs PostgreSQL Code Snippets PostgreSQL News PWN EMC Events Externes Geocaching gequälte Sprache Hardware Kino Languages C make Perl PHP PL/pgSQL Shell SQL TeX Links myself Ausgehen Diving Familie Fotografie Hochzeit Unterwegs AIDA Die Bahn Online Pivotal Politisches Steuern Regionales Berlin Glienicke/Nordbahn Hamburg Magdeburg Sachsen-Anhalt Schönebeck/Elbe Wien Social networks Software Android Ansible Apple iPod iTunes KDE Operating Systems Linux S9y Typo3 Stupid Vereinsleben Freifunk Glienicke/Nordbahn PGUG Europe PGUG Germany UniHelp Last Search content MVB Magdeburg Preise -install "locales" package ubuntu 15 reuse of unused copy parken gehweg Zettel Münzen als geschenke hochzeitstorte schokolade psql on_error_stop Statistics Last entry: 2016-10-12 01:38 917 entries written 564 comments have been made 12091 visitor(s) this month 151 visitor(s) this week 15