CODEWITHSUNDEEP
bashpostgresqlpostgresql-10
user3541631
user3541631

Reputation: 4008

Catch a psql(PostgreSQL) command error in bash, that can be used generic, indifferent of the sql

I want to catch a PostgreSQL error in bash.

For example:

function create_database:
    sudo -u postgres psql -c "CREATE DATABASE $1 WITH OWNER $2;"

I want something that can catch any type of postgres errors (not only for create) and echo the error

also in case of error return 1

If I use: $RESULT=$(sudo -u postgres psql -c "CREATE DATABASE $1 WITH OWNER $2;")

I get the answer from psql but is specific to the operation, so I have to do string matching for each SQL command.

Upvotes: 0

Views: 5958

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

It is fairly trivial to see if the statement was successful or not: just check the return code.

$ sudo -u postgres psql -c 'melect 32'
ERROR:  syntax error at or near "melect"
LINE 1: melect 32
        ^
$ echo $?
1

$ sudo -u postgres psql -c 'DROP TABLE not_exists'
ERROR:  table "not_exists" does not exist
$ echo $?
1

$ sudo -u postgres psql -c 'SELECT 42'
 ?column? 
----------
       42
(1 row)

$ echo $?
0

So your code could do something like this:

sudo -u postgres psql -c "..." >/tmp/result 2>&1
if [ $? -ne 0 ]; then
    echo /tmp/result >>logfile
    rm -f /tmp/result
    exit 1
else
    rm -f /tmp/result
fi

Upvotes: 4

Related Questions