PostgreSQL如何设置主键自增（序列、SERIAL）

PostgreSQL如何设置主键自增

背景

在使用Mysql时，创建表结构时可以通过关键字auto_increment来指定主键是否自增。但在Postgresql数据库中，虽然可以实现字段的自增，但从本质上来说却并不支持Mysql那样的自增。

在MySQL当中，我们可以通过勾选来实现ID自增，我们的navcat如下图所示：

pgsql没有auto_increment这么一说，所以想要建立自增主键就得想想其他办法了!

什么是序列

序列是 PostgreSQL 中的一种特殊对象，用于生成唯一的数字值，通常用于表的主键字段。序列保证了在并发插入时，生成的 ID 唯一且自增。

Postgresql的自增机制

在PostgreSQL中，实现表中的id列作为主键并设置为自增，通常涉及到使用序列（SEQUENCE）和设置默认值的概念。

方式1：
使用SERIAL或BIGSERIAL数据类型是最简便的方式，它们会自动创建序列并设置默认值。

使用SERIAL或BIGSERIAL数据类型时，PostgreSQL会自动为我们创建一个序列并将它设为对应字段的默认值，这能极大简化开发过程。

例如，当我们希望id字段自增时，只需在建表时将id字段的类型指定为SERIAL（对于较小的整数）或BIGSERIAL（对于较大的整数），无需手动创建序列或编写额外的代码来管理主键的增长。

方式2：
使用CREATE SEQUENCE与ALTER TABLE命令手动创建序列并设置默认值。

基本使用

使用SERIAL或BIGSERIAL数据类型

Postgresql中字段的自增是通过序列来实现的。
Postgresql提供了三种serial数据类型：smallserial，serial，bigserial。它们与真正的类型有所区别，在创建表结构时会先创建一个序列，并将序列赋值给使用的字段。
这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

PostgreSQL的SERIAL不是真正的数据类型，而是在创建表时的一个快捷方式，用于自动创建支持自增的序列。

如果尚未创建表，可用如下方法

方法一：

create table test_a (

  id serial,
  name character varying(128),
  constraint pk_test_a_id primary key( id)
);

方法二：

create table test_b(

  id serial PRIMARY KEY,
  name character varying(128)
); 

这两种方法，会自动创建名为表名_字段名_seq的序列，且MAXVALUE=9223372036854775807

这两种方法用的是pg的serial类型实现自增，drop表的时候指定的序列也会drop掉。

这种使用方式，每次向表中插入新记录时，id列将自动增长，无需手动指定id值。

手动创建序列和设置默认值

先创建主键表

create table test_c (

  id integer PRIMARY KEY,
  name character varying(128)
);  

使用CREATE SEQUENCE命令创建一个序列。例如：

CREATE SEQUENCE test_c_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

将序列赋值给主键
创建序列后，需要通过ALTER TABLE命令来显式设置某列的默认值为该序列的下一个值：

alter table test_c alter column id set default nextval('test_c_id_seq');

这种方法在drop表的时候序列不会随着drop掉，这种方式，seq是一个独立的东西，插入语句，不能忽略id字段。

实战demo：PostgreSQL 手动序列管理

1. 删除序列

DROP SEQUENCE IF EXISTS xxx_seq;

• DROP SEQUENCE: 删除指定的序列。
• IF EXISTS: 如果序列不存在，则不会抛出错误，而是安全地跳过此操作。
• xxx_seq: 指定要删除的序列。

2. 创建序列
   创建序列是生成唯一数字值的重要步骤。以下是创建序列的 SQL 示例。

CREATE SEQUENCE xxx_seq
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

• CREATE SEQUENCE: 创建一个新的序列。
• INCREMENT 1: 每次调用 nextval 时，序列的值增加 1。
• MINVALUE 1: 序列的最小值为 1。
• MAXVALUE 9223372036854775807: 序列的最大值，设置为 64 位整数的最大值。
• START 1: 序列的初始值为 1。
• CACHE 1: 指定缓存的序列值数量，可以提高性能。设置为 1 意味着每次只缓存一个值。

3. 使用序列
   一旦序列创建完成，您可以使用 nextval 函数获取下一个值，并将其插入到表中。

INSERT INTO t_test (id, name)
VALUES (nextval('xxx_seq'), 'admin2');

nextval: 调用序列的下一个值。
将序列值作为 id 插入到 t_test 表中，确保每条记录的 ID 唯一。

4. 查看序列信息
   您可以查询系统视图以查看序列的当前状态。

SELECT * FROM xxx_seq;

说明
此查询将返回序列的当前值、增量、最小值和最大值等信息。

设置序列的当前值

SELECT setval('xxx_seq', 1, false);

• setval 是 PostgreSQL 提供的一个函数，用于设置序列的当前值。
• xxx_seq 是一个标识符，表示您要操作的序列。
• 1， 这是要设置的值。此语句将序列的当前值设置为 1。
• false 这是一个布尔参数，指示下次调用 nextval 时序列的行为：
  • false: 表示下次调用 nextval 时，序列将返回 2（即在设置的值上加 1）。
  • true: 如果设置为 true，则下次调用 nextval 时将返回 1（即从设置的值开始）。

这条语句的效果是将序列 xxx_seq 的当前值设置为 1，并且在下次获取下一个值时，序列将返回 2。这通常用于重置序列，以确保新的 ID 从特定值开始，常见于数据清理或重新插入数据的场景。

工作常用总结

创建表时候自定义序列：id SERIAL PRIMARY KEY 和 id int8 NOT NULL DEFAULT nextval(…) 的主要区别

• SERIAL:
  SERIAL 是一种 PostgreSQL 特定的数据类型，它实际上是一个简写，用于创建一个整型列并同时创建一个序列。
  当使用 SERIAL 时，PostgreSQL 会自动为该列创建一个序列，并将该列的默认值设置为调用该序列的 nextval 函数。
  例如，id SERIAL PRIMARY KEY 会隐式执行下面的操作:

id int4 NOT NULL DEFAULT nextval('some_seq');

• int8 NOT NULL DEFAULT nextval(…):
  种方式更显式地定义了列的类型和默认值。
  您可以选择使用 int8（64 位整数），并手动指定要使用的序列名称和行为。
  这种方式更加灵活，允许开发者在表定义中自定义序列的名称和特性。
  通过显式定义序列，您可以在多个表之间共享同一个序列，确保 ID 的唯一性，同时在需要时也能灵活地调整序列的行为。这种方式提供了更大的控制力，适用于复杂的数据库设计需求。

id SERIAL PRIMARY KEY 和 id int8 NOT NULL DEFAULT nextval(…) 的主要区别

• 使用 SERIAL:

适合简单场景，快速定义自增主键。
可能不够灵活，无法自定义序列的行为或名称。

• 使用 int8 和 nextval(…):

提供更大的控制和灵活性。
可以指定不同的序列名，允许在多个表中使用同一个序列，或者在需要时替换序列。

总结：如果只需要简单的自增 ID，使用 SERIAL 更为简洁。如果需要更大的灵活性和控制，尤其是在管理序列时，使用 int8 NOT NULL DEFAULT nextval(…) 是更好的选择。