Postgres: Non zero exit code when executing a sql file?

Question

I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f).

e.g. psql -c "create table foo (bar integer)"

psql -f foobar.sql

One difference between these forms is that a call by command (-c) returns a non-zero exit code if an error is encountered, while a call by file (-f) always seems to return zero.

I'm wondering if there is a workaround for this behaviour? (i.e. return non-zero if an error occurs while executing a file).

Thanks.

Solution 1

I found out how to resolve this.

I need to enable ON_ERROR_STOP at the top of the file.

Example:

\set ON_ERROR_STOP true

Solution 2

You can use below statement.

psql -v ON_ERROR_STOP=1 -f foobar.sql

This will return the correct return code even foobar.sql file doesn't enable ON_ERROR_STOP at the top of the file.

Comments

[Brandon]

+1 for the best solution because it allows me to set the variable in the script, so I can trust it always runs correctly and don't have to worry about runtime context.

[Frug]

good lord this has come in handy