PostgreSQL internal variable ON_ERROR_STOP

PostgreSQL internal variable ON_ERROR_STOP defaults to 0 for interactive common execution, such as the client program psql.

 

So if you execute multiple statements in a script, if an earlier statement had errors, it would not stop executing the remaining sql statements, although it shows some errors, the transaction would not be rolled back for the succesuflly executed ones.

 

This variable can be set with the "set" command, like "\set ON_ERROR_STOP 1", within the psql client program. Once turned on, it would stop processing remaining statements once an error occurred. The following screeshot shows what it means:

  

One thing to notice is that if executed from pgAdmin or the psql command line, it seems like to wrapped in one single transaction, thus if there's any error, the whole thing would be rolled back. For instance:

 

>psql -U jwang -d jwTest -c "select x; alter table jwTest.users alter column mobile type varchar(10);"
Password for user jwang:
ERROR:  column "x" does not exist
LINE 1: select x; alter table jwTest.users alter column mobile type ...
               ^
>psql -U jwang -d jwTest -c "alter table jwTest.users alter column mobile type varchar(10); select x;"
Password for user jwang:
ERROR:  column "x" does not exist
LINE 1: ...jwTest.users alter column mobile type varchar(10); select x;