Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 614
Naveen Reddy CH Avatar asked May 06 '16 12:05

Naveen Reddy CH


People also ask

What does psql return?

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.

What is the psql command to end a psql session?

The meta-command for exiting psql is \q .

What is a psql shell?

Connect PostgreSQL Database using SQL Shell (psql) SQL Shell is a command-line tool to connect and work with the PostgreSQL database. You can use it to create, alter, delete databases, tables, etc. in the PostgreSQL database.

Which command can be used in psql to show all the available?

A single Postgres server process can manage multiple databases at the same time. Each database is stored as a separate set of files in its own directory within the server's data directory. To view all of the defined databases on the server you can use the \list meta-command or its shortcut \l .


2 Answers

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 
like image 175
Daniel Vérité Avatar answered Oct 18 '22 23:10

Daniel Vérité


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

\set ON_ERROR_STOP true 
like image 38
Marco Roy Avatar answered Oct 18 '22 23:10

Marco Roy