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.

score 44 · Answer 1 · edited Jun 27 '12 at 04:27

44

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.

edited Jun 27 '12 at 04:27

mgorven

30,036
7
76
121

answered Jun 10 '11 at 09:11

Taye

441
4
2

2

good lord this has come in handy – Frug Mar 27 '20 at 02:13

score 24 · Accepted Answer · answered Jun 09 '09 at 18:51

24

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

answered Jun 09 '09 at 18:51

Jin Kim

963
2
9
12

+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. – Brandon Dec 27 '14 at 04:48

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

2 Answers2