CODEWITHSUNDEEP
postgresqlshell
Naveen Reddy CH
Naveen Reddy CH

Reputation: 849

Check return status of psql command in unix shell scripting

I am using psql command to connect and issue a query on postgreSQL database. Can anybody let me know how to check the return status of the executed query in shell script.

I have used echo $? command to check the status but it always returning zero.

Thanks for the help.

Upvotes: 40

Views: 30485

Answers (2)

Marco Roy
Marco Roy

Reputation: 5305

As mentioned here, you can also add this line at the top of your SQL file/script:

\set ON_ERROR_STOP true

Upvotes: 6

Daniel Vérité
Daniel Vérité

Reputation: 61726

psql return code is documented as:

EXIT STATUS
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.

You probably just want to use ON_ERROR_STOP.

Failure getting tested and reported to the shell:

$ psql -d test -v "ON_ERROR_STOP=1" <<EOF
select error;
select 'OK';
EOF

ERROR:  column "error" does not exist
LINE 1: select error;

$ echo $?
3

Failure getting ignored and not reported to the shell:

$ psql -d test  <<EOF
select error;
select 'OK';
EOF
ERROR:  column "error" does not exist
LINE 1: select error;
               ^
 ?column? 
----------
 OK
(1 row)

$ echo $?
0

Upvotes: 79

Related Questions