Postgres自动递增主键

Question

我想弄清楚一个自动增量键是如何在Postgres中表示的，这取决于你是否使用SERIAL类型或使用IDENTITY创建它。

当我使用这个DDL创建一个带有SERIAL主键的表时:

CREATE TABLE public.test_change_column (
id SERIAL PRIMARY KEY NOT NULL
)

我得到一个序列称为'test_change_column_id_seq'，并给出列id的默认值nextval('test_change_column_id_seq'::regclass)

如果我使用这个DDL创建一个没有SERIAL和主键的表:

CREATE TABLE public.test_change_column (
id INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
)

创建序列，但id的默认值没有设置为nextval('test_change_column_id_seq'::regclass)。相反，is_identity列被设置为"YES"

如果您创建的列没有自动递增，则不能在以后使用SERIAL:

添加它。

ALTER TABLE public.test_change_column  ALTER COLUMN id TYPE serial; 

这将导致错误"type "serial"不存在"

使用IDENTITY方法，您可以使用以下DDL添加自动增量:

ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

下面的SQL将展示postgres如何为这两种不同的方法存储元数据:

SELECT column_name, column_default, is_identity 
FROM information_schema.columns 
WHERE table_name = 'test_change_column';

当删除自动增量时，自动增量的删除方式不同，这取决于您使用的是串行增量还是标识增量。

如果自动增量是使用串行类型创建的，则必须ALTER COLUMN id DROP DEFAULT。这不会删除关联的序列表。

如果自动增量是使用IDENTITY创建的，则必须ALTER COLUMN id DROP IDENTITY。这也删除了序列表。

Answer 1

将列更改为标识列(并添加PK约束)就足够了，这是正确的做法(不鼓励使用serial伪类型)

你可以看到，例如psql报告这个正确的身份列:

arthur=> CREATE TABLE public.test_change_column (id INTEGER NOT NULL);
CREATE TABLE
arthur=> d public.test_change_column
Table "public.test_change_column"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id     | integer |           | not null |

arthur=> ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE
arthur=> ALTER TABLE public.test_change_column ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
ALTER TABLE
arthur=> d public.test_change_column
Table "public.test_change_column"
Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
id     | integer |           | not null | generated by default as identity
Indexes:
"test_change_column_pkey" PRIMARY KEY, btree (id)

您还可以通过插入一行来验证默认值是否正常工作:

insert into public.test_change_column default values;

将创建一个新行，并将增加id值。

这样的列将显示在information_schema.columns和is_identity = 'YES'中，而不是具有默认值。

---

我建议使用generated always as identity，这样您就不会意外地通过序列绕过生成(其效果是序列和表中的值不再"同步")。

Answer 2

创建表

CREATE TABLE public.test_change_column (
id INTEGER NOT NULL
);

修改表:

ALTER TABLE 
public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY, 
ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
d test_change_column
Table "public.test_change_column"
Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
id     | integer |           | not null | generated by default as identity
Indexes:
"test_change_column_pkey" PRIMARY KEY, btree (id)

IDENTITY列有一个与之相关的序列，就像serial列一样。它只是没有显示为DEFAULT。查找底层序列:

select pg_get_serial_sequence ('public.test_change_column', 'id');
pg_get_serial_sequence      
----------------------------------
public.test_change_column_id_seq
select * from public.test_change_column_id_seq ;
last_value | log_cnt | is_called 
------------+---------+-----------
1 |       0 | f

您可以使用下面的IDENTITY命令来操作上面的命令

ALTER [COLUMN] column_name ADD GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequence_options)]

ALTER [COLUMN] column_name {SET GENERATED {ALWAYS | BY DEFAULT} | SET sequence_option | RESTART [[WITH] RESTART]}[…]

ALTER [COLUMN] column_name DROP IDENTITY [IF EXISTS]

From this serial type:

数据类型smallserial, serial和bigserial不是真类型，而仅仅是为了方便创建唯一标识符列(类似于其他一些数据库支持的AUTO_INCREMENT属性)。在当前的实现中，指定:

CREATE TABLEcolname串行

);

相当于指定:

CREATE SEQUENCE tableame_colname_seq AS integer;

CREATE TABLEnextval('tablename_colname_seq')

);

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

因此，我们创建了一个整数列，并安排从序列生成器为其分配默认值。应用NOT NULL约束以确保不能插入空值。(在大多数情况下，您还需要附加UNIQUE或PRIMARY KEY约束，以防止意外插入重复值，但这不是自动的。)最后，该序列被标记为"属于"该列，因此，如果该列或表被删除，它将被删除。

因此，如果您想要ALTER表来复制serial'type'，则需要执行上述单个步骤:

CREATE TABLE public.test_change_column (id INTEGER NOT NULL);
create sequence test_change_column_seq;

alter 
table test_change_column alter COLUMN id 
set default nextval('test_change_column_seq'), 
ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
alter sequence test_change_column_seq owned by test_change_column.id;
d test_change_column
Table "public.test_change_column"
Column |  Type   | Collation | Nullable |                   Default                   
--------+---------+-----------+----------+---------------------------------------------
id     | integer |           | not null | nextval('test_change_column_seq'::regclass)
Indexes:
"test_change_column_pkey" PRIMARY KEY, btree (id)