38

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.

Naveen Reddy CH
  • 799
  • 2
  • 13
  • 23

2 Answers2

76

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
raphael
  • 2,762
  • 5
  • 26
  • 55
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • This doesn't seem to be triggered with permission errors. I had a command exit with status 0 – raphael Sep 25 '19 at 13:48
  • @raphael: might be a bug, do you have a test case to share? – Daniel Vérité Sep 25 '19 at 14:37
  • Are there system tables that a non-super user wouldn't be able to access? – raphael Sep 25 '19 at 18:31
  • @raphael: `pg_catalog.pg_authid` – Daniel Vérité Sep 25 '19 at 19:10
  • ah.... I think the issue is I was pipeing between two commands and the first one was failing. `psql -v "ON_ERROR_STOP=1" -c "\COPY pg_catalog.pg_authid TO STDOUT;" | cat` produces `ERROR: permission denied for relation pg_authid`, but an exit code of 0. Going to use this going forward https://stackoverflow.com/questions/1221833/pipe-output-and-capture-exit-status-in-bash – raphael Sep 25 '19 at 19:49
  • 1
    @raphael: see `set -o pipefail` in bash to fail the pipeline on error – Victor Roetman Dec 01 '21 at 13:50
4

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

\set ON_ERROR_STOP true
Marco Roy
  • 4,004
  • 7
  • 34
  • 50