Exit shell script if psql throws error in exectuting sql scripts

Question

I have below shell script

#! /bin/sh

for file in $(ls -l scripts/*.sql | awk '{print $NF}'); do 
 psql -h host -d db -U user -p port -f $file;
 result=$?
 if [ $result -ne 0 ] 
   then
   exit 1
 fi
done

$result always comes 0 even if I add some error to sql file. I want to exit shell script if any sql script throws error. Am I missing something in catching the error of last executed statement

try -b Print failed SQL commands to standard error output. This is equivalent to setting the variable ECHO to errors. — Digvijay S
– Digvijay S, Commented Apr 6, 2020 at 8:11
@Jetchisel Thanks this link is useful to update script specially around legacy code — user13164016
– user13164016, Commented Apr 6, 2020 at 9:47

pifor · Accepted Answer · 2020-04-06 08:19:02Z

5

Try: psql -v ON_ERROR_STOP=1 -f $file

This makes psql stopping at first SQL error and returning a non-zero return code in this case.

answered Apr 6, 2020 at 8:19

pifor

7,9722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exit shell script if psql throws error in exectuting sql scripts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related