Run SQL from a file

A migration script passed with psql -f hits an error in the second statement, carries on and exits successfully.

cat > errors.sql <<'EOT'
SELECT 'one' AS step;
SELECT * FROM table_that_does_not_exist;
SELECT 'three' AS step;
EOT
psql -f errors.sql; echo "exit status: $?"
echo ---
psql -v ON_ERROR_STOP=1 -f errors.sql; echo "exit status: $?"

 step
------
 one
(1 row)

psql:errors.sql:2: ERROR:  relation "table_that_does_not_exist" does not exist
LINE 1: SELECT * FROM table_that_does_not_exist;
                      ^
 step
-------
 three
(1 row)

exit status: 0
---
 step
------
 one
(1 row)

psql:errors.sql:2: ERROR:  relation "table_that_does_not_exist" does not exist
LINE 1: SELECT * FROM table_that_does_not_exist;
                      ^
exit status: 3

By default psql reports an error and goes on with the next statement, and its exit status says only whether it could run at all, so the first run prints a result for step three and exits 0. Setting the ON_ERROR_STOP variable makes psql stop at the first error, and the exit status becomes 3, which a script can test. Both behaviors, and the list of exit codes, are in the psql reference.

ON_ERROR_STOP does not wrap the file in a transaction, so statements before the error stay applied; add --single-transaction or -1 for that. Without it a migration can be half applied. Use -X to ignore ~/.psqlrc, which can change the behavior of a script without anyone noticing.