postgresql 遇到错误时如何停止 Postgres 脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4480381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I stop a Postgres script when it encounters an error?
提问by Ray
Is there a way to specify that when executing a sql script it stops when encountering the first error on the script, it usually continues, regardless of previous errors.
有没有办法指定在执行 sql 脚本时它在遇到脚本上的第一个错误时停止，它通常会继续，而不管以前的错误如何。
回答by Alfishe
I think the solution to add following to .psqlrc is far from perfection
我认为将以下内容添加到 .psqlrc 的解决方案远非完美
\set ON_ERROR_STOP on
there exists much more simple and convenient way - use psql with parameter:
存在更简单方便的方法 - 使用带参数的 psql：
psql -v ON_ERROR_STOP=1
better to use also -Xparameter turning off .psqlrc file usage.
Works perfectly for me
最好还使用-X参数关闭 .psqlrc 文件使用。非常适合我
p.s. the solution found in great post from Peter Eisentraut. Thank you, Peter! http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html
ps 在 Peter Eisentraut 的精彩帖子中找到的解决方案。谢谢你，彼得！ http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html
回答by plundra
I assume you are using psql, this might be handy to add to your ~/.psqlrcfile.
我假设您正在使用psql，这可能很方便添加到您的~/.psqlrc文件中。
\set ON_ERROR_STOP on
This will make it abort on the first error. If you don't have it, even with a transaction it will keep executing your script but fail on everything until the end of your script.
这将使它在第一个错误时中止。如果你没有它，即使有一个事务，它也会继续执行你的脚本，但在你的脚本结束之前一切都失败了。
And you probably want to use a transaction as Paul said. Which also can be done with psql --single-transaction ...if you don't want to alter the script.
你可能想使用保罗所说的交易。psql --single-transaction ...如果您不想更改脚本，也可以这样做。
So a complete example, with ON_ERROR_STOP in your .psqlrc:
所以一个完整的例子，在你的 .psqlrc 中有 ON_ERROR_STOP：
psql --single-transaction --file /your/script.sql
回答by Paul Tomblin
It's not exactly what you want, but if you start your script with begin transaction;and end with end transaction;, it will actually skip everything after the first error, and then it will rollback everything it did before the error.
这并不完全是您想要的，但是如果您以 开始begin transaction;并以 结束脚本end transaction;，它实际上会在第一个错误之后跳过所有内容，然后它会回滚在错误之前所做的一切。
回答by Gregory Arenius
I always like to reference the manual directly.
我总是喜欢直接参考手册。
From the PostgreSQL Manual:
Exit Status
psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.
退出状态
如果 psql 正常完成，则向 shell 返回 0，如果发生自身的致命错误（例如，内存不足，找不到文件），则返回 1，如果与服务器的连接出现故障并且会话没有交互，则返回 3，如果脚本中发生错误并且设置了变量 ON_ERROR_STOP。
By default if the sql code you are running on the PostgreSQL server error psql won't quit an error. It will catch the error and continue. If, as mentioned above, you set the ON_ERROR_STOPsetting to on, when psql catches an error in the sql code it will exit and return 3to the shell.
默认情况下，如果您在 PostgreSQL 服务器上运行的 sql 代码错误 psql 不会退出错误。它将捕获错误并继续。如果如上所述，您将ON_ERROR_STOP设置设置为 on，则当 psql 在 sql 代码中捕获错误时，它将退出并返回3到 shell。
