Exit codes for nzsql on UNIX systems

On UNIX systems such as the NPS, the exit codes from the nzsql command provide information about the execution status of the SQL commands.

When you are running queries interactively at the nzsql prompt or by using a script (nzsql -f), the command returns one of the following exit codes:

0: All queries were successful.
1: At least one query was successful.
2: All queries failed.

For example, the mytest.sql file contains a query that selects from an invalid table (T11):

[user@nzhost nz]$ nzsql -f mytest.sql
nzsql:mytest.sql:1: ERROR:  Relation 'T11' does not exist
[user@nzhost nz]$ echo $?
2

When you are running a query with the nzsql -c command, the exit codes are:

0: Query succeeded
-1 or 255: SQL failed (issues with syntax, failed authentication, permissions)
-2 or 254: Connection failed
-3 or 253: User cancellation request
-4 or 252: Session was terminated

On many platforms, return codes that are outside the range of 0 to 255 are not supported. On Linux® and UNIX platforms, -1 through -4 are converted modulo 256, therefore the values -1 and 255 are equivalent.

[user@nzhost nz]$ nzsql -c "select * from t11"
ERROR:  Relation 'T11' does not exist
[user@nzhost nz]$ echo $?
255

The nzsql -f command runs all statements in the file. If you want the nzsql command to stop and exit when it encounters an error with a query, include -v ON_ERROR_STOP=1 on the command line. The exit code is 1 if there were successful queries before the failed query, or 2 if the first query failed.

If you create an ON_ERROR_STOP block inside a query file, as in this example:

<initial SQL queries>
\set ON_ERROR_STOP ON
     <queries inside block> 
\unset ON_ERROR_STOP
<trailing SQL queries>

The success or failure of queries in the initial SQL command section or the trailing SQL command section are ignored when there is an ON_ERROR_STOP block. The success or failure of the commands inside the block determine the exit value. The exit codes are 0 (all queries in the block succeeded), 1 (some queries were successful), or 2 (all queries inside the block failed). If you do not include the command to unset the ON_ERROR_STOP block, the block ends at the end of the file.