错误时如何继续执行sql脚本？ [英] How to continue sql script on error?

问题描述

我们有几个迁移脚本，这些脚本会在版本之间更改模式。

We have a couple of migration scripts, which alter the schema from version to version.

有时会发生迁移步骤（例如，将列添加到表）已经手动完成或通过补丁安装完成，因此迁移脚本失败。

Sometimes it happens, that a migration step (e.g. adding a column to a table) was already done manually or by a patch installation, and thus the migration script fails.

如何防止脚本因错误而停止（理想情况下是出现特定预期错误） ），而是记录一条消息并继续执行脚本？

How do I prevent the script from stopping on error (ideally at specific expected errors) and instead log a message and continue with the script?

我们使用PostgresQL 9.1，既可以使用PostgresQL解决方案，也可以使用常规的SQL解决方案。

We use PostgresQL 9.1, both a solution for PostgresQL as well as a general SQL solution would be fine.

推荐答案

尽管@LucM的答案似乎是一个不错的建议-@TomasGreif指出了我的答案，但更多的是针对我的原籍请求

Although @LucM's answer seems to be good recommendation - @TomasGreif pointed me to an answer that went more in the direction of my origin request.

对于添加列的给定示例，这可以通过使用 DO 语句来捕获。例外：

For the given example of adding a column, this can be done by using the DO statement for catching the exception:

DO $$ 
    BEGIN
        BEGIN
            ALTER TABLE mytable ADD COLUMN counter integer default 0; 
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'counter column already exists';
        END;
    END;
$$;

此提示将我引向正确的PostgresQL网站，描述了错误代码一个人可能会捕获-因此这对我来说是正确的解决方案。

The hint led me to the right PostgresQL site, describing the error codes one could trap - so that was the right solution for me.

这篇关于错误时如何继续执行sql脚本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！