PostgreSQL中自增列和自增ID的正确打开方式
MySQL转到PG后很多小伙伴遇到的问题就是PostgreSQL中的主键ID自增问题(其他解决方式很多这里就不关注了)
那如果你的应用场景中需要用到自增列,应该怎么操作呢?网上大部分给出的解决方法都是首先创建一个sequence
,然后配置某一列的默认值,即可实现。但是这无疑对于我们这种懒人来说是一个无(lan)法(de)翻(dong)越(tan)的大山,而且在建表的时候还需要先去创建个序列,简直就是多此一举?而且创建序列的时候的名称自由度太高?所以一直很抵触这种方式,直到今天看到了一篇文章:《PostgreSQL SERIAL创建自增列》,才发现眼前一亮,豁然开朗,茅厕顿开,一泻千里!
所以我们就把这篇文章原(shao)封(jia)不(xiu)动(gai)搬到了这里,如果你觉得好,请给原作者点赞!关注!比心!
PostgreSQL SERIAL创建自增列
本文我们介绍PostgreSQL SERIAL,并展示如何使用serial类型创建表自增列。
PostgreSQL SERIAL伪类型
PostgreSQL序列是一种特殊的用于生产整数序列数据库对象。序列通常用于主键列,与mysql的AUTO_INCREMENT 概念类似。创建表时使用serial伪类型定义序列:
CREATE TABLE table_name(
id SERIAL
);
赋值serial伪类型给id列,PostgreSQL将执行下列步骤:
- 创建序列对象并设置下一个生成值作为列的缺省值。
- 给对应列增加NOT NULL约束,因为序列总是生成一个整数值,不能为null值。
- 赋值序列的拥有者给id列,因此当id列或表被删除时,序列对象自动被删除。
对应背后执行语句:
CREATE TABLE table_name(
id SERIAL
);
相当于执行了下面语句:
CREATE SEQUENCE table_name_id_seq;
CREATE TABLE table_name (
id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);
ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;
PostgreSQL 提供三种序列伪类型,分别为SMALLSERIAL, SERIAL, BIGSERIAL,对应范围如下:
Name | Storage Size | Range |
---|---|---|
SMALLSERIAL | 2 bytes | 1 to 32,767 |
SERIAL | 4 bytes | 1 to 2,147,483,647 |
BIGSERIAL | 8 bytes | 1 to 922,337,2036,854,775,807 |
PostgresQL SERIAL示例
特别需要注意的是,serial列不会隐式在列上创建索引或是该列为主键列。当然可以很容易使用PRIMARY KEY关键字增加相应约束。
下面语句创建fruits表,其中id类是serial类型:
CREATE TABLE fruits(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
在insert语句中,可以忽略对应列或使用default关键字都可以给serial赋值,请看示例:
INSERT INTO fruits(name) VALUES('orange');
或者:
INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');
PostgreSQL 给fruits表中插入两行记录,id的值为1和2:
SELECT * FROM fruits;
id | name
----+--------
1 | apple
2 | orange
(2 rows)
使用pg_get_serial_sequence
函数可以获得serial列
的序列名称:
pg_get_serial_sequence('table_name','column_name')
通过给currval函数
传入序列名称可以获得当前生成的值。举例,下面语句返回最近生成的值:
SELECT currval(pg_get_serial_sequence('fruits', 'id'));
返回结果:
currval
---------
2
(1 row)
当执行insert语句是,你想获得序列生成的值,可以在insert语句中使用RETURNING id子句实现。下面语句插入一行新的记录,并返回id列生成的值:
INSERT INTO fruits(name) VALUES('banana')
RETURNING id;
结果如下:
id
----
3
(1 row)
序列生成器不是事务安全的。意味着如果两个并发数据库连接尝试从序列中获取下一个值,每个客户端获得不同的值。如果一个客户端回滚事务,则该序列值将被弃用,导致序列值不连续。
总结
本文我们学习如何使用serial数据类型,用于给数据表创建自增列。
有了serial伪类,我们在建表的时候再也不用去手动创建sequence
啦!直接整就可以啦!
补充
今天在开发中突然发现,通过这个方式创建的自增ID列,在执行Upsert
的时候也会增加,即INSERT ON CONFLICT DO UPDATE
的时候,即使因为冲突导致执行了UPDATE
操作,但是SEQUENCE
也会增加。具体如何解决没有探究。