Postgresql Psql Exit On Error
Contents |
use psql in an interactive mode where we connect to the database and then type and execute queries. But sometimes it psql on_error_rollback is useful to execute a whole SQL file. We can do this with:
Psql Command Line
psql -U username -d database -f file.sql The problem with executing an sql file like this is that psql psql commands will exit with a status code 0 (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 psql continue on error end of file, which is not what we would 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
Man Psql
| grep "ERROR:" This would work, but it's not a very clean solution and 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 - Ex
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 on_error_stop=1 [option...] [dbname [username]] Description psql is a terminal-based front-end to PostgreSQL. It psql run sql file enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be
Psqlrc
from a file. In addition, it provides a 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 http://blog.nordeus.com/dev-ops/psql-exit-on-first-error.htm input lines to standard output as they are read. (This does 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 https://www.postgresql.org/docs/9.2/static/app-psql.html (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 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 more about Stack Overflow the company Business Learn more about hiring developers or posting ads http://dba.stackexchange.com/questions/3904/postgresql-exit-status-when-running-a-file 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 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 PostgreSQL exit status when running a file up vote 12 down vote favorite on error When running PostgreSQL with a single SQL command error codes are returned as expected: % psql -c "SELECT * FROM AWDASDASDASDAS" my_db ERROR: relation "awdasdasdasdas" does not exist LINE 1: SELECT * FROM AWDASDASDASDAS % echo $? 1 But when running a file, the error is suppressed: % psql -f test.sql my_db psql:test.sql:1: ERROR: relation "awdasdasdasdas" does not exist LINE 1: SELECT * FROM AWDASDASDASDAS % echo $? 0 Any idea how to get these errors back? postgresql share|improve this question asked Jul 17 postgresql psql exit '11 at 8:43 Adam Matan 2,460104271 Huh? Report a bug against psql maybe? –alex Jul 17 '11 at 10:09 It can be a reasonably intentional behavior. –Adam Matan Jul 17 '11 at 14:34 a very similar question has been answered on serverfault.com –ssc Sep 19 at 7:57 add a comment| 1 Answer 1 active oldest votes up vote 8 down vote Adding this to the first line of the SQL file does the trick: \set ON_ERROR_STOP 1 share|improve this answer answered Jul 17 '11 at 15:21 Adam Matan 2,460104271 Ugh Postgres... That's annoying. –Jmoney38 Dec 23 '14 at 13:05 another way: cat myfile.sql | psql -v ON_ERROR_STOP=1 . This is nice because it isn't necessary to modify the SQL file, the checking can be done only via the command line. –johntellsall Aug 11 '15 at 17:14 This doesn't work for me. Do you have any idea why? Doesn't work -v ON_ERROR_STOP=1 as well. I am under Postgres 9.5. –pt12lol Aug 31 at 8:02 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged postgresql or ask your own question. asked 5 year