psql return value / error killing the shell script that called it?

Question

I'm running several psql commands inside a bash shell script. One of the commands imports a csv file to a table. The problem is, the CSV file is occasionally corrupt, it has invalid characters at the end and the import fails. When that happens, and I have the ON_ERROR_STOP=on flag set, my entire shell script stops at that point as well.

Here's the relevant bits of my bash script:

$(psql \
-X \
 $POSTGRES_CONNECTION_STRING \
-w \
-b \
-L ./output.txt
-A \
-q \
--set ON_ERROR_STOP=on \
-t \
-c "\copy mytable(...) from '$input_file' csv HEADER"\
)

echo "import is done"

The above works fine as long as the csv file isn't corrupt. If it is however, psql spits out a message to the console that begins ERROR: invalid byte sequence for encoding "UTF8": 0xb1 and my bash script apparently stops cold at that point-- my echo statement above doesn't execute, and neither do any other subsequent commands.

Per the psql documentation, a hard stop in psql should return an error code of 3:

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

That's fine and good, but is there a reason returning a value of 3 should terminate my calling bash script? And can I prevent that? I'd like to keep ON_ERROR_STOP set to on because I actually have other commands I'd like to run in that psql statement if the intial import succeeds, but not if it doesn't.

Answer 1

ON_ERROR_STOP will not work with the -c option.

Also, the $(...) surronding the psql look wrong — do you want to execute the output as a command?

Finally, you forgot a backslash after the -L option

Try using a “here document”:

psql \
  -X \
  $POSTGRES_CONNECTION_STRING \
  -w \
  -b \
  -L ./output.txt \
  -A \
  -q \
  --set ON_ERROR_STOP=on \
  -t <<EOF
\copy mytable(...) from '$input_file' csv HEADER
EOF

echo "import is done"