Stop on the first error

A migration script has three statements, the second fails, and psql still reports success.

printf 'CREATE TABLE t1 (id int);\nSELECT * FROM missing_table;\nCREATE TABLE t2 (id int);\n' > migrate.sql
psql -q -f migrate.sql; echo "default exit: $?"
psql -q -c "DROP TABLE IF EXISTS t1, t2" 2>/dev/null
psql -q -v ON_ERROR_STOP=1 -f migrate.sql; echo "ON_ERROR_STOP exit: $?"
psql -At -c "SELECT tablename FROM pg_tables WHERE tablename IN ('t1','t2') ORDER BY 1"

ERROR:  relation "missing_table" does not exist
LINE 1: SELECT * FROM missing_table;
                      ^
default exit: 0
ERROR:  relation "missing_table" does not exist
LINE 1: SELECT * FROM missing_table;
                      ^
ON_ERROR_STOP exit: 3
t1

By default psql prints an error and goes on with the next statement, and its exit status reflects only whether it could run, so the first run finishes with status 0 even though a statement failed. Setting ON_ERROR_STOP makes it stop at the first error and exit with status 3, as the psql reference documents. The last query shows that t2 was never created in the second run.

The statements before the error were already committed, since each runs in its own transaction. Add -1 (--single-transaction) to wrap the whole file in one, so a failure undoes everything. A statement that uses CREATE INDEX CONCURRENTLY cannot run in a transaction block.