Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A psql script prints ERROR lines, runs on, and the shell sees success

ErrorERROR: relation "orders" does not exist

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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: $?"
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 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. 2psql stopped, but half of the script's changes are in the databaseThe statements before the error are already committedFix
    3. 3The 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

What does the error look like?

  • ERROR: relation "orders" does not exist
  • echo $? 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 on

3Verify

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

Did this fix it?

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.sql

2Fix

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.sql

3Verify

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

Did this fix it?

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" ~/.psqlrc

2Fix

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.sql

3Verify

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.