Postgresql 添加串行列(如果不存在)仍在创建序列

Question

我调用命令

ALTER TABLE tableName ADD COLUMN IF NOT EXISTS columnName SERIAL

在PostgreSQL中。首次运行时，将根据需要使用相应的序列创建一个新列。但是，当再次运行时，将在数据库中创建另一个序列，即使该列已存在也是如此。有谁知道任何修复程序可以阻止这个新序列的创建？

Answer 1

看起来你发现了一个小错误。我可以在 Postgres 9.6 和 10 中重新创建它：

db<>小提琴在这里

事实上，它在 2017 年 9 月就已经报道过。到目前为止，修复的尝试已被拒绝，因为它会引入其他奇怪之处。

为了规避，请考虑劳伦茨回答中的建议。也许可以使用DO命令进行自动化，例如：

DO
$do$
BEGIN
IF NOT EXISTS (SELECT FROM pg_attribute
WHERE  attrelid = 'public.tblname'::regclass  -- table name here 
AND    attname = 'colname'                    -- column name here
AND    NOT attisdropped
) THEN
ALTER TABLE public.tblname ADD COLUMN IF NOT EXISTS colname serial;
ELSE
RAISE NOTICE 'column "colname" of relation "public.tblname" already exists, skipping';
END IF;
END
$do$;

如果多个事务可以同时更改相同的表结构，则检查和创建之间可能存在争用条件。

生成的通知也正是您在IF NOT EXISTS中得到的。

将其包装到一个 plpgsql 函数中，动态 SQL 获取参数以重复使用任意表名和列名。

相关：

• 创建 PostgreSQL 角色(用户((如果不存在(

对表名进行架构限定以避免歧义。字符串在这里不区分大小写。您不能在问题中使用 CaMeL 大小写拼写。看：

• PostgreSQL 列名是否区分大小写？

Answer 2

您可以使用以下步骤，这些步骤具有相同的效果：

ALTER TABLE tablename ADD COLUMN IF NOT EXISTS columnname integer;

现在，如果未创建列并且您收到通知，则已完成。否则继续：

CREATE SEQUENCE IF NOT EXISTS tablename_columnname_seq
OWNED BY tablename.columnname;
ALTER TABLE tablename ALTER COLUMN columnname
SET DEFAULT nextval('tablename_columnname_seq');

或者，您可以在创建列之前检查该列是否存在：

SELECT count(*) FROM information_schema.columns
WHERE table_name = 'tablename' AND column_name = 'columnname';

您可能希望将架构名称添加到条件中。