如何在Postgres中创建类似于SQL Server的自增列(identity column)？

Question

有一个应用程序使用MSSQL作为其后端。现在我正在开发代码，以便它可以使用PostgreSQL。除了执行以下操作时存在一个区别外，我几乎已经完成了所有工作：

保存新应用程序时，

SQL Server代码：

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer not null IDENTITY
)

PostgreSQL 代码：

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20)
)

为什么在通过PostgreSQL执行时，towid字段（默认字段）不会自动生成？

有可能是什么原因？触发器？过程？

- Anu Shibin Joseph Raj

---

在Postgres中使用towid serial not null：https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL - user330315

Answer 1

简述

在Postgres 10中，可以按照SQL标准指定 GENERATED BY DEFAULT AS IDENTITY。

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer GENERATED BY DEFAULT AS IDENTITY    -- per SQL standard
)

自增列

Postgres 10 现在支持标准 SQL 语法的 自增列。虽然我不是 MS SQL Server 的专家，但我相信这个新的标准支持是等价的。

GENERATED … AS IDENTITY

在 CREATE TABLE 中使用 GENERATED … AS IDENTITY 命令会创建一个隐式序列。创建、命名、权限和删除该序列对您来说是透明的，与 SERIAL 不同。现在非常直观了。如果您给表授予权限，则它们将获得序列的访问权限。如果您删除表，则序列将自动删除。

有两种标准语法，区别只有在传递值而不是生成值时才有意义。通常，人们总是依赖于生成的值，因此通常您只需要使用第一个版本：GENERATED BY DEFAULT AS IDENTITY。

• GENERATED BY DEFAULT AS IDENTITY
  • 生成一个值，除非 INSERT 命令提供一个值。
• GENERATED ALWAYS AS IDENTITY
  • 忽略由 INSERT 提供的任何值，除非指定 OVERRIDING SYSTEM VALUE

请参见CREATE TABLE页面进行文档编写。

阅读 Peter Eisentraut 的这篇有趣的文章。他解释了一些关于 SERIAL 的奇怪问题。使用新的自增列功能就没有这样的问题了。因此现在没有理由再使用 SERIAL，没有缺点，只有好处；SERIAL已被GENERATED … AS IDENTITY取代。

请注意，自增列不一定是主键，并且不会自动索引。因此，如果您的意图是这样的话，仍然需要明确指定 PRIMARY KEY。

CREATE TABLE person_ (

    id_ 
        INTEGER 
        GENERATED BY DEFAULT AS IDENTITY   -- Replaces SERIAL. Implicitly creates a SEQUENCE, specified as DEFAULT.
        PRIMARY KEY                        -- Creates index. Specifies UNIQUE. Marks column for relationships.
        ,

    name_ 
        VARCHAR( 80 )

) ;

这是为了隐藏内部实现细节，无需您知道在幕后生成的序列名称。例如，您可以通过列重置计数器，而不必知道底层序列。

ALTER TABLE person_ 
    ALTER COLUMN id_ 
    RESTART WITH 1000      -- Reset sequence implicitly, without a name.
;

隐式指定身份:

• 标记列 NOT NULL
• 创建序列
  • 序列类型与列匹配（32位、64位等）
• 将序列绑定到该列上
  • 继承权限
  • 级联删除
  • 即使重命名列，也仍然绑定到该列上
• 将序列指定为该列的默认值来源

身份列可以采用与CREATE SEQUENCE相同的选项：

• START WITH start
• MINVALUE minvalue | NO MINVALUE
• MAXVALUE maxvalue | NO MAXVALUE
• INCREMENT [ BY ] increment
• CYCLE | NO CYCLE
• CACHE cache
• OWNED BY NONE
  (为身份列指定所有权对我来说没有意义，因为所有权是自动管理的)

选项的荒谬示例：

id_ INTEGER 
GENERATED BY DEFAULT AS IDENTITY ( 
    START WITH 200 
    MINVALUE 100 
    MAXVALUE 205 
    CYCLE 
    INCREMENT BY 3 
) PRIMARY KEY

添加4行：

200

203

100

103

- Basil Bourque

Answer 2

PostgreSQL将根据您的请求创建表。它不会自动生成一些列（几乎是这样，但这是低级别的）。您需要在请求中添加towid serial primary key。

- Ivan Burlutskiy

---

好的...但是没有纯SQL代码可以生成towid字段...它会通过存储过程或其他方式生成吗？ - Anu Shibin Joseph Raj

@AnuShibinJosephRaj：serial是与identity相当的东西。有关详细信息，请参阅手册：https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL - user330315

PostgreSQL的serial或bigserial类型是T-SQL中IDENTITY类型的良好替代品。它们保证唯一值并提供易于理解的值。IDENTITY有时可能会表现得有些奇怪... - JosMac

更新：Postgres 10 支持标准语法 GENERATED ... AS IDENTITY。除了符合标准 SQL 外，这个新特性还修复了 SERIAL 的一些奇怪问题。请阅读 Peter Eisentraut 在 2017-04 发布的文章。 - Basil Bourque