Postgresql 如果不存在则添加序列列仍会创建序列

Question

我执行该命令

ALTER TABLE tableName ADD COLUMN IF NOT EXISTS columnName SERIAL

在PostgreSQL中，第一次运行此命令时，将创建一个新的列，并创建一个相应的序列。但是，当再次运行时，即使该列已经存在，也会在数据库中创建另一个序列。有没有人知道如何修复这个问题，以防止创建新的序列？

- wfawwer

---

在执行“alter table”之前，请检查列是否存在。据推测，在检查表中是否存在该列之前，序列已被创建。 - Gordon Linoff

Gordon是正确的。请参阅"8.1.4. Serial Types"。 - sticky bit

Answer 1

您可以使用以下步骤，其效果相同：

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';

你可能想要在条件中添加模式名称。

- Laurenz Albe

Answer 2

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

db<>fiddle 在这里

实际上，它已经在2017年9月被报告。迄今为止，试图修复它已经被拒绝，因为它会引入其他奇怪的问题。

为了规避这个问题，请考虑Laurenz的建议。也许可以使用像这样的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时得到的完全相同。 将其封装在一个具有动态SQL的plpgsql函数中，以便重复使用带有任意表格和列名称的参数。 相关：

• 如果不存在则创建PostgreSQL ROLE（用户）

为避免歧义，请对架构限定表名。这里的字符串不区分大小写。您不能使用问题中拥有的CaMeL-case拼写。详情请参见：

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

- Erwin Brandstetter