问题描述
我有一个 PostgreSQL 查询,其中包含一个事务和一个列重复时的异常:
I have an PostgreSQL query that includes a transaction and an exception if a column is duplicated:
BEGIN;
ALTER TABLE "public"."cars"
ADD COLUMN "top_speed" text;
EXCEPTION WHEN duplicate_column THEN NOTHING;
ROLLBACK;
在这个查询中,我试图添加一个已经存在的列(有点例外),如果它存在,那么查询将忽略它.目前我不确定我使用的异常代码是否正确(找不到描述它们的站点;只找到 这个)
In this query I am trying to add a column that already exists (playing a little bit with exceptions) and if it does then the query shall just ignore it. At the moment I am not really sure if the exception-code I am using is the right (couldn't find a site where they are described; only found this)
我的问题是,如果我执行此查询,我会收到错误消息:
My Problem is if I execute this query I get the error-message:
错误:关系cars"的top_speed"列已经存在
如果我第二次执行它,错误消息将更改为:
And if I execute it a second time the error-message changes to:
错误:当前事务被中止,命令被忽略直到事务块结束
推荐答案
尝试 匿名代码块
.正如 Laurenz 所提到的,您正在混合 PL/pgSQL 和 SQL 命令.
Try an anonymous code block
. As Laurenz mentioned, you were mixing PL/pgSQL and SQL commands.
示例表
CREATE TABLE t (f1 TEXT);
匿名代码块
DO $$
BEGIN
IF (SELECT count(column_name) FROM information_schema.columns
WHERE table_schema = 'public' AND
table_name = 't' AND
column_name = 'f2') = 0 THEN
ALTER TABLE public.t ADD COLUMN "f2" text;
END IF;
END$$;
执行后,您将拥有新列.如果该列已存在,则不会执行任何操作.
After execution you have your new column. If the column already exists, it will do nothing.
SELECT * FROM t;
f1 | f2
----+----
0 Zeilen
在 PostgreSQL 9.6+ 中,您可以使用 IF NOT EXISTS
来检查给定的列在创建之前是否已经存在于表中:
In PostgreSQL 9.6+ you can use IF NOT EXISTS
to check if a given column already exists in the table before creating it:
ALTER TABLE t ADD COLUMN IF NOT EXISTS f2 TEXT;
代码位于 db<>fiddle
一个>
这篇关于使用 EXCEPTION 执行 PostgreSQL 查询会导致两种不同的 ERROR 消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!