FAQ: Using Sequences in PostgreSQL
Many of the questions asked in #postgresql revolve around using sequences in PostgreSQL. To avoid answering the same questions again and again, I thought it would be worthwhile to summarize the basic steps involving in using sequences in PostgreSQL.
What Is A Sequence?
A sequence is a special kind of database object designed for generating unique numeric identifiers. It is typically used to generate artificial primary keys. Sequences are similar, but not identical, to the AUTO_INCREMENT concept in MySQL.
How Do I Use A Sequence In A Table?
Sequences are most commonly used via the serial
pseudotype. A serial
is a special data type that encodes the following information:
- it indicates that the values for the column will be generated by consulting the sequence
- therefore, it creates a new sequence object, and sets the default value for the column to be the next value produced by the sequence
- since a sequence always produces non-NULL values, it adds a NOT NULL constraint to the column
- since the sequence that is produced is created "behind the scenes", PostgreSQL assumes that the sequence is only used to generate values for the table containing the serial column. Therefore, if this column is dropped, the sequence will be automatically removed.
For example, this command creates both a new table and a new sequence generator, and associates the sequence with the id
column of the table:
test=# CREATE TABLE users ( test(# id SERIAL, -- assign each user a numeric ID test(# name TEXT, test(# age INT4 test(# ); NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" CREATE TABLE
In this case, the sequence is automatically assigned the name users_id_seq. To avoid hard-coding the name of the sequence in SQL queries, we can use thepg_get_serial_sequence() function, as described below.
Note that using serial
does not implicitly create an index on the column, or mark the column as a primary key. That can be easily done, however:
CREATE TABLE users ( -- make the "id" column a primary key; this also creates -- a UNIQUE constraint and a b+-tree index on the column id SERIAL PRIMARY KEY, name TEXT, age INT4 );
How Do I Assign Sequence Values To Newly-Inserted Rows?
If you're using serial
, the default value for the serial column will be the next value produced by the sequence. To specify that an INSERT should take the default value for a given column, either omit that column from the INSERT's column list, or specify theDEFAULT keyword as the column's value.
Usage example:
INSERT INTO users (name, age) VALUES ('Mozart', 20);
Or equivalently:
INSERT INTO users (name, age, id) VALUES ('Mozart', 20, DEFAULT);
How Do I Retrieve The Most Recent Value Generated By A Sequence?
You can use the currval() function, which returns the most recent value generated by a sequence for the current session. currval() takes a single parameter: the name of the sequence. We can use the function pg_get_serial_sequence() to find the name of the sequence associated with a given serial column:
SELECT currval(pg_get_serial_sequence('users', 'id'));
Note that if no values have been generated by the sequence yet in the current session,currval() will yield an error.
Isn't This Subject To A Race Condition?
That is, if one database client inserts a row into a table that includes a sequence-generated value, wouldn't it be possible for another insertion into the table to modify the sequence, causing a subsequent currval() by the first client to return the wrong results?
No: sequences were designed to elegantly avoid this problem. currval() returns the last value generated by the sequence for the current session: if concurrent database clients generate sequence values, the currval() seen by a given session does not change (until the session generates a new sequence value, for example).
Doesn't Invoking currval() Require Two Database Queries?
To use the currval() method shown above, we'd need two queries: one to insert into the table, and another to fetch the sequence value assigned to the new row. Since client-server roundtrips can be expensive, this is not ideal. One way around this is to send the INSERT and the SELECT as a single query string. For example, in PHP:
pg_exec("INSERT INTO users (name, age) VALUES ('Bach', 15); SELECT currval(pg_get_serial_sequence('users', 'id'));")
This executes two queries, but does only a single roundtrip between the client and server, so the additional performance overhead of the second query should be negligible.
Alternatively, users of PostgreSQL 8.2 and later can take advantage of the INSERT ... RETURNING clause:
INSERT INTO users (name, age) VALUES ('Liszt', 10) RETURNING id;
which returns the value of the id
column for the newly-inserted row.
What Is The Range Of Values Generated By A Sequence?
Sequences generate 64-bit signed integers. The serial
pseudotype that we used above is a 32-bit signed integer: if you want to use the full 64-bit range of the underlying sequence, use the serial8
pseudotype instead.
Can There Be "Gaps" In The Values Generated By A Sequence?
Yes, there can. Sequences are intended for generating unique identifiers — not necessarily identifiers that are strictly sequential. If two concurrent database clients both attempt to get a value from a sequence (using nextval()), each client will get a different sequence value. If one of those clients subsequently aborts their transaction, the sequence value that was generated for that client will be unused, creating a gap in the sequence.
This can't easily be fixed without incurring a significant performance penalty. For more information, see Elein Mustein's "Gapless Sequences for Primary Keys" in the General Bits Newsletter.
What About Transactions?
Sequence operations are essentially non-transactional. nextval() increments the value of the sequence and is not rolled back if its transaction is later aborted; currval()returns the last value generated by the sequence for the current session, regardless of transaction boundaries.
What If I Want To Share One Sequence Between Two Tables?
The easiest way to do this is to create the sequence by hand, and then set the default clauses for the sequence-generated columns by hand, rather than using the serial
type:
CREATE SEQUENCE common_fruit_id_seq; CREATE TABLE apples ( id INT4 DEFAULT nextval('common_fruit_id_seq') NOT NULL, price NUMERIC ); CREATE TABLE oranges ( id INT4 DEFAULT nextval('common_fruit_id_seq') NOT NULL, weight NUMERIC );
nextval() is a function that produces a new sequence value.
Note that when using sequences in this manner, the sequence won't be automatically dropped when the table is dropped, and you won't be able to usepg_get_serial_sequence().
Where Can I Find More Information About Sequences?
Consult the PostgreSQL documentation:
- Serial Types
- Sequence Manipulation Functions (currval(), nextval(), etc.)
- CREATE SEQUENCE
- DROP SEQUENCE
相关推荐
JDBC 连接到 PostgreSQL 数据库获取数据简单例子,最小的代码实现数据库读取
使用Jmeter往PostgreSQL大象数据库中插入测试数据; GUID的自动生成,大象数据库的链接配置
PostgreSQL获取数据库中所有table名及所有字段名,以及获取指定table的所有字段信息
PostgreSQL 使用序列来标识字段的自增长,数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。 使用 MySQL 设置自动增长的语句如下: CREATE TABLE IF NOT ...
ruoyi框架集成postgresql,对自增表设置序列
Python + Postgresql + PostGIS爬虫获取某地图POI数据 (1)网络爬虫 (2)多线程 (3)数据库连接池
postgresql
将excal批量数据插入到postgresql数据库(目前实现字符串拼接的方式)。。
用于自动安装postgresql,只需要先配置好yum源,运行此脚本即可安装完成postgresql
使用postgresql客户端备份数据比较慢,并且使用客户端备份时不能进行其他操作,使用脚本备份比较方便。 注意事项: 1.使用本脚本 要修改成你自己要备份的数据库、连接IP、用户名、密码 2.备份前要用postgresql客户端...
Postgresql
PostgreSQL中文学习手册 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (数据表 数据表 ) 4 一、表的定义: 一、表的定义: 一、表的定义: . 4 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册...
PostgreSQL(postgresql-13.5.tar.bz2) PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。...
创建函数,通过调用函数获取输入的身份证校验码(输入的身份证长度在17位以上,没有做输入验证)
PostgreSQL Configuration
postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql ...
postgresql\mysql 的逆向工程(生成的代码含:controller、service、serviceImpl、entity、mapper、mapper.xml)
Navicat for PostgreSQL是一套专为PostgreSQL设计的强大数据库管理及开发工具。它可以用于任何版本 7.5 或以上的 PostgreSQL 数据库服务器,并支持大部份 PostgreSQL最新版本的功能,包括触发器、函数、管理用户等。...