How to check if .sql script execution succeeded?

Question

I'm loading an exported database with the below commands:

psql -c 'drop database database1'
psql -c 'create database database1'
psql database1 < script.sql

Now I'm trying to check if the final command succeeded:

if [[ $? -eq 0 ]]; then
    echo "OK."
else
    echo "Not OK."
fi

This always outputs "OK." the Exit code is always 0, even if script.sql completes with errors:

psql database1 < script.sql
ERROR: constraint "test_id" for relation "test" already exists
echo $?
0

How can I confirm the sql import succeeded?

This thread might help: https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error — Adam vonNieda, Apr 30 '19 at 12:55
Possible duplicate of [Check return status of psql command in unix shell scripting](https://stackoverflow.com/questions/37072245/check-return-status-of-psql-command-in-unix-shell-scripting) In short: Instead try: `psql -v "ON_ERROR_STOP=1" database1 < script.sql` and test the results of that. You should get return code `3`. See the duplicate I flagged for more info. — JNevill, Apr 30 '19 at 12:55
Looks like it does not properly set the return code. Either parse the output text or configure it to return with a proper error code. — Anubis, Apr 30 '19 at 12:56

score 1 · Answer 1 · answered Apr 30 '19 at 12:55

1

You can do check if the command throws an error like this:

psql database1 < script.sql || echo 'error occurred'

The double pipe || means "Do the left part and if error do the right part of the pipes".

Hope it helps!

answered Apr 30 '19 at 12:55

Thomas Baier

1 Answers1