reading errors returned from psql in a bash script

Question

I need to check for errors when running psql from a bash script. Here's an example of how we're running things in the script:

return_value=$(psql \
-X \
 $POSTGRES_CONNECTION_STRING \
-f ./build_table.sql \
-w \
-b \
-A \
-q \
-t \
)

psql_exit_status=$?

The above statement works fine unless there's an error in the sql script, in which case I get some error output on the console but return_value is set to zero, as is psql_exit_status.

The build_table sql script creates a table and imports data from a csv file-- if there are errors in the csv file, or if, say, I intentionally misspell create tableeeee in the sql script I see psql errors on the screen but no error info is returned as best I can tell.

I've tried using the -o flag in psql to output to a file. Nothing shows, it's a blank file. I've also tried adding a 2>&1 bit after the psql statement to see if I could get some error info that way, but nothing doing.

What I need is some way to tell that the sql script has exited abnormally and/or crashed, without having to look at the output on the screen. Is that possible with the way I'm executing psql? Possible something's up with one of my psql flags?

Answer 1

From the psql documentation:

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.

So you need to add -v ON_ERROR_STOP=on to your psql options.

However setting this variable has the following side effect:

By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.