Skip to main content
 首页 » 数据库

PostgreSQL SERIAL创建自增列

2022年07月19日132exmyth

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数据类型,用于给数据表创建自增列。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/93369058
阅读延展