原创 红石PG 红石PG

 2025年03月07日 07:32 陕西

数据库中经常会用到自增列，而 PostgreSQL 中使用自增列的方式就有好几种。那么，哪种方式更好呢？

介绍

从 PostgreSQL 10 开始，它支持一个名为 “标识列” 的特性，它的工作原理与 serial 列非常相似：

CREATE TABLE test_old (
    id serial PRIMARY KEY,
    payload text
);

INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;
复制

和

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);

INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;
复制

两者做的事情是几乎相同的，只是新的方法更冗长。那么，它们有什么区别吗？

兼容性

新的标识列语法符合 SQL 标准。创建自动递增列，一直是不同 SQL 实现之间不兼容的一个出了名的地方。但是，有些应用开发者最近一直在采用标准 SQL 语法。因此，现在您可以在 PostgreSQL、DB2 和 Oracle 之间迁移代码，而无需在自增列的地方进行任何更改。

权限

旧的 serial 方法的一个普遍问题是，系统实际上不记得用户键入了 serial。它在解析时将其展开成了如下的内容：

CREATE SEQUENCE test_old_id_seq;

CREATE TABLE test_old (
    id int NOT NULL PRIMARY KEY,
    payload text
);

ALTER TABLE test_old
    ALTER COLUMN id SET DEFAULT nextval('test_old_id_seq');

ALTER SEQUENCE test_old_id_seq OWNED BY test_old.id;
复制

最后一个命令中的 OWNED BY，试图记住有关序列性的一些信息，但在某些情况下它仍然不够。

新的标识列方法，将序列创建为表的一个适当的内部依赖项，因此不会暴露出 serial 伪类型的各种奇怪的实现细节。

一个常见的问题是，一个 serial 列创建的序列的权限需要单独进行管理：

CREATE USER foo;
GRANT INSERT ON test_old TO foo;
GRANT INSERT ON test_new TO foo;

SET SESSION AUTHORIZATION foo;

INSERT INTO test_old (payload) VALUES ('d');
ERROR:  permission denied for sequence test_old_id_seq

INSERT INTO test_new (payload) VALUES ('d');
-- OK
复制

您还可以通过运行下面命令，修复错误：

GRANT USAGE ON SEQUENCE test_old_id_seq;
复制

如果您有部署脚本，这很烦人且成问题，因为序列的名称是自动生成的。当然，它出现在错误消息中，并且很容易猜到，但有时会选择略有不同的名称，然后部署脚本将会失败。

管理序列

如果要对序列进行一些更改，您还需要知道序列的名称：

ALTER SEQUENCE test_old_id_seq RESTART WITH 1000;
复制

使用标识列，您无需知道序列的名称：

ALTER TABLE test_new ALTER COLUMN id RESTART WITH 1000;
复制

表结构管理

由于 serial 不是一个真实类型，因此它只能在某些情况下使用。您可以在创建表或添加列时指定 serial 作为列类型。但是，删除一个现有列的序列性，或要添加序列性到一个现有列，并不简单。

要删除序列性，您可以使用 CASCADE 选项删除序列（同样，需要在确定名称之后），该选项会级联删除关联列的默认值：

DROP SEQUENCE test_old_id_seq CASCADE;
复制

如果您改为删除默认值，例如：

ALTER TABLE test_old ALTER COLUMN id DROP DEFAULT;
复制

它将删除默认值，但保留序列。

如果要获取一个现有的整型列，并将其转换为 serial 列，则没有单个命令可以做到。您必须手动组装如前面显示的 CREATE SEQUENCE 和 ALTER TABLE ... SET DEFAULT 命令。

删除一个现有列的标识属性很容易：

ALTER TABLE test_new ALTER COLUMN id DROP IDENTITY;
复制

你不可能意外地去误删除一个默认值，因为根本没有默认值。但是万一你误删除了，您会收到一条不错的错误消息：

ALTER TABLE test_new ALTER COLUMN id DROP DEFAULT;
ERROR:  column "id" of relation "test_new" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.
复制

您还可以使用一个命令，将一个现有的整型列转换为一个标识列：

ALTER TABLE test_new
    ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
复制

复制表结构

如果使用 CREATE TABLE / LIKE 功能复制一个表的结构，则 serial 列会带来问题：

CREATE TABLE test_old2 (LIKE test_old INCLUDING ALL);

INSERT INTO test_old2 (payload) VALUES ('e') RETURNING *;
 id | payload
----+---------
  4 | e
复制

请注意，即使新表是一个单独的表，它也会继续使用旧的序列。

当您想要删除原先的表时，这会变得更加混乱：

DROP TABLE test_old;
ERROR:  cannot drop table test_old because other objects depend on it
DETAIL:  default for table test_old2 column id depends on sequence test_old_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
复制

您可以按照提示使用 CASCADE，也可以先删除 test_old2。后者可以在不带 CASCADE 的情况下完成，因为序列仍然链接到第一个表。

当您以这种方式复制一个有标识列的表时，您将获得一个新的序列：

CREATE TABLE test_new2 (LIKE test_new INCLUDING ALL);

INSERT INTO test_new2 (payload) VALUES ('e') RETURNING *;
 id | payload
----+---------
  1 | e
复制

升级

也许你已经倾向于使用标识列了，并且你想要把你所有杂乱的 serial 列升级成这个新的标识列。（请注意，您不必执行 “升级”。您可以继续像以前一样使用 serial 列。）这是您可以使用的一个 PL/pgSQL 函数：

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  colnum smallint;
  seqid oid;
  count int;
BEGIN
  -- find column number
  SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'column does not exist';
  END IF;

  -- find sequence
  SELECT INTO seqid objid
    FROM pg_depend
    WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
      AND classid = 'pg_class'::regclass AND objsubid = 0
      AND deptype = 'a';

  GET DIAGNOSTICS count = ROW_COUNT;
  IF count < 1 THEN
    RAISE EXCEPTION 'no linked sequence found';
  ELSIF count > 1 THEN
    RAISE EXCEPTION 'more than one linked sequence found';
  END IF;  

  -- drop the default
  EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) || ' DROP DEFAULT';

  -- change the dependency between column and sequence to internal
  UPDATE pg_depend
    SET deptype = 'i'
    WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
      AND deptype = 'a';

  -- mark the column as identity column
  UPDATE pg_attribute
    SET attidentity = 'd'
    WHERE attrelid = tbl
      AND attname = col;
END;
$$;
复制

可以这样来调用它：

SELECT upgrade_serial_to_identity('test_old', 'id');
复制

如果您使用了模式，则它看起来像这样：

SELECT upgrade_serial_to_identity('public.test_old', 'id');
复制

在操作之前和之后，检查您的表定义：

=> \d test_old
                             Table "public.test_old"
 Column  |  Type   | Collation | Nullable |               Default
---------+---------+-----------+----------+--------------------------------------
 id      | integer |           | not null | nextval('test_old_id_seq'::regclass)
 payload | text    |           |          |
复制