A psql script prints ERROR lines, runs on, and the shell sees success
ERROR: relation "orders" does not existShort answer
By default psql carries on after a failed command, so a script can print errors and still finish normally. Run it with -v ON_ERROR_STOP=on. psql then stops at the first error and exits with status 3. Add --single-transaction to roll back everything that ran before that error.
Fix it fast
1Find out which cause you have
Run the script and print psql's exit status right after it. A status of 0 next to ERROR lines means psql did not stop and finished normally.
psql -d mydb -f script.sql echo "exit status: $?"2Match what you see, then jump to its fix
- 1
ERROR lines appear in the middle of the output, later statements still run, and the exit status is 0ON_ERROR_STOP is not set, and the default is to continueFix - 2
psql stopped, but half of the script's changes are in the databaseThe statements before the error are already committedFix - 3
The script has BEGIN and COMMIT, an ERROR line appears, and the rest still commitsON_ERROR_ROLLBACK is on, so failed statements are skipped inside the transactionFix
- 1
What does the error look like?
ERROR: relation "orders" does not existecho $? prints 0 after a script that printed ERROR lines
Cause 1 of 3
ON_ERROR_STOP is not set, and the default is to continue
1Check
By default command processing continues after an error. psql returns 0 when it finished normally, and 3 only when an error occurred in a script and ON_ERROR_STOP was set.
psql -d mydb -f script.sql
echo "exit status: $?"2Fix
Set the variable on the command line. With it on, psql stops immediately and ends any running scripts, including ones started from the main file. You can also put the \set line at the top of the script.
psql -d mydb -v ON_ERROR_STOP=on -f script.sql
-- or as the first line of script.sql
\set ON_ERROR_STOP on3Verify
After a failing statement, psql stops there and the exit status is 3.
psql -d mydb -v ON_ERROR_STOP=on -f script.sql
echo "exit status: $?"Confirmed by postgresql.org
Cause 2 of 3
The statements before the error are already committed
1Check
Without a transaction around the file, each statement takes effect as soon as it runs, so stopping does not undo the earlier ones, and the result is a partially applied script. Look for BEGIN in the file.
grep -n -i -E "^(begin|commit|rollback)" script.sql2Fix
Wrap the whole run in one transaction with --single-transaction (-1). With ON_ERROR_STOP set, psql sends ROLLBACK instead of COMMIT if a command fails. Do not combine it with a script that has its own BEGIN or COMMIT, and a command that cannot run inside a transaction block makes the whole transaction fail.
psql -d mydb --single-transaction -v ON_ERROR_STOP=on -f script.sql3Verify
Run it with a deliberately broken last statement. The tables show none of the earlier changes.
psql -d mydb -c "\dt"Confirmed by postgresql.org
Cause 3 of 3
ON_ERROR_ROLLBACK is on, so failed statements are skipped inside the transaction
1Check
When ON_ERROR_ROLLBACK is on, an error inside a transaction block is ignored and the transaction continues. It works through an implicit savepoint before each command. The default is off. A start-up file such as ~/.psqlrc can set it.
grep -n "ON_ERROR" ~/.psqlrc2Fix
Run scripts with -X so psql reads no start-up file, and keep ON_ERROR_ROLLBACK off for them.
psql -X -d mydb --single-transaction -v ON_ERROR_STOP=on -f script.sql3Verify
A failing statement now ends the run with exit status 3, and nothing from the transaction is committed.
psql -X -d mydb --single-transaction -v ON_ERROR_STOP=on -f script.sql
echo "exit status: $?"Confirmed by postgresql.org
How do I stop it coming back?
- Start every deployment or migration script with psql -X -v ON_ERROR_STOP=on, so it behaves the same on every machine.
- Check the exit status of psql in your CI or shell script, and treat any non-zero value as a failed step.
- Use --single-transaction for scripts that must be all or nothing, and split out commands that cannot run inside a transaction block.
- Test a script against a copy of the database first, and add -b if you want failed commands echoed to the error output.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
psql prints invalid command \N many times during a restore
invalid command \N
The \N lines are data rows, not commands.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix