1. Introduction
Sequence objects (also called sequence generators) are special single-row tables created with CREATE SEQUENCE. A sequence object is usually used to generate unique identifiers for rows or tables.
2. Create a sequence
Method 1: Directly specify the field type to serial type in the table
david=# create table tbl_xulie ( david(# id serial, david(# name text); NOTICE: CREATE TABLE will create implicit sequence "tbl_xulie_id_seq" for serial column "tbl_xulie.id" CREATE TABLE david=#
Method 2: Create the sequence name first, and then specify the sequence in the column attribute in the newly created table. The column requires the int type
Syntax for creating sequences:
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { | NONE } ]
Example:
david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;
CREATE SEQUENCE
david=#
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#
3. View the sequence
david=# \d tbl_xulie
Table "public.tbl_xulie"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
id | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
name | text |
david=# \d tbl_xulie2
Table "public.tbl_xulie2"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
id | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
name | text |
david=#
View sequence properties
david=# \d tbl_xulie_id_seq
Sequence "public.tbl_xulie_id_seq" Column | Type | Value ---------------+---------+--------------------- sequence_name | name | tbl_xulie_id_seq last_value | bigint | 1 start_value | bigint | 1 increment_by | bigint | 1 max_value | bigint | 9223372036854775807 min_value | bigint | 1 cache_value | bigint | 1 log_cnt | bigint | 0 is_cycled | boolean | f is_called | boolean | f Owned by: public.tbl_xulie.id
david=# select * from tbl_xulie2_id_seq;
sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called -------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- tbl_xulie2_id_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | f (1 row)
IV. Sequence application
4.1 Using sequences in INSERT commands
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+------- 1 | David 2 | Sandy (2 rows)
4.2 Update sequence after data migration
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=#
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Eagle');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Miles');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Simon');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Rock');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Peter');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sally');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Nicole');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Monica');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Renee');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+-------- 15 | Sandy 16 | David 17 | Eagle 18 | Miles 19 | Simon 20 | Rock 21 | Peter 22 | Sally 23 | Nicole 24 | Monica 25 | Renee (11 rows)
david=# copy tbl_xulie to '/tmp/tbl_xulie.sql';
COPY 11
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 100;
ALTER SEQUENCE
david=# select currval('tbl_xulie_id_seq');
currval
--------- 25 (1 row)
david=# select nextval('tbl_xulie_id_seq');
nextval
--------- 100 (1 row)
david=# select nextval('tbl_xulie_id_seq');
nextval --------- 101 (1 row)
david=# begin;
BEGIN
david=# copy tbl_xulie from '/tmp/tbl_xulie.sql';
COPY 11
david=# select setval('tbl_xulie_id_seq', max(id)) from tbl_xulie;
setval
-------- 25 (1 row)
david=# end;
COMMIT
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+-------- 15 | Sandy 16 | David 17 | Eagle 18 | Miles 19 | Simon 20 | Rock 21 | Peter 22 | Sally 23 | Nicole 24 | Monica 25 | Renee 26 | Flash (12 rows)
david=# select nextval('tbl_xulie_id_seq');
nextval
--------- 27 (1 row)
V. Sequence functions
The following sequence function provides a simple and concurrent read-safe method for us to obtain the latest sequence value from the sequence object.
5.1 View the next sequence value
david=# select nextval('tbl_xulie_id_seq');
nextval
---------
3
(1 row)
david=# select nextval('tbl_xulie_id_seq');
nextval
---------
4
(1 row)
5.2 View the most recent use of sequence values
david=# select nextval('tbl_xulie_id_seq');
nextval
---------
4
(1 row)
david=# select currval('tbl_xulie_id_seq');
currval
---------
4
(1 row)
david=# select currval('tbl_xulie_id_seq');
currval
---------
4
(1 row)
5.3 Reset the sequence
Method 1: Use sequence functions
a. setval(regclass, bigint)
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1);
setval
-------- 1 (1 row)
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+------- 2 | Sandy 3 | David (2 rows)
david=# select currval('tbl_xulie_id_seq');
currval
---------
3
(1 row)
david=# select nextval('tbl_xulie_id_seq');
nextval
---------
4
(1 row)
b. setval(regclass, bigint, boolean)
b.1 setval(regclass, bigint, true)
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, true);
setval
-------- 1 (1 row)
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+------- 2 | Sandy 3 | David (2 rows)
The effect is the same as a. setval(regclass, bigint)
b.2 setval(regclass, bigint, false)
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, false);
setval
-------- 1 (1 row)
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+------- 1 | Sandy 2 | David (2 rows)
Method 2: Modify the sequence
Modify the syntax of the sequence:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ]
[ RESTART [ [ WITH ] restart ] ]
[ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { | NONE } ]
ALTER SEQUENCE name OWNER TO new_owner
ALTER SEQUENCE name RENAME TO new_name
ALTER SEQUENCE name SET SCHEMA new_schema
Example:
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 0;
ERROR: RESTART value (0) cannot be less than MINVALUE (1)
david=# alter sequence tbl_xulie_id_seq restart with 1;
ALTER SEQUENCE
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;
id | name ----+------- 1 | David 2 | Sandy (2 rows)
david=# select nextval('tbl_xulie_id_seq');
nextval
---------
3
(1 row)
6. Delete the sequence
grammar:
DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
When a table field is used in a PG sequence, it cannot be deleted directly.
david=# drop sequence tbl_xulie2_id_seq; ERROR: cannot drop sequence tbl_xulie2_id_seq because other objects depend on it DETAIL: default for table tbl_xulie2 column id depends on sequence tbl_xulie2_id_seq HINT: Use DROP ... CASCADE to drop the dependent objects too. david=# drop table tbl_xulie2; DROP TABLE david=# drop sequence tbl_xulie2_id_seq; DROP SEQUENCE david=#
Note: For a sequence that is created by the specified serial when creating a table, the corresponding sequence will also be deleted while deleting the table.
7. Other instructions
What is obtained is the sequence value of the current session. In the current session, the value will not change because other sessions have taken nextval. What will change is the global last_value value, and if you have not read the nextval value in the current session, you will report an error.
b. When the sequence is created by specifying the serial time when creating the table, the corresponding sequence will also be deleted.
c. The table primary key data can be used with a table-related sequence or other sequences, but it is not recommended, it is just that PG defaults to it.
d. In order to make the same sequence value not repeated in concurrently, nextval will not rollback, but you can use setval to reset
If a sequence object is created with the default parameters, calling nextval on it will return the subsequent numeric value starting from 1. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE command; refer to its command reference page for more information.
e. In order to avoid the current transaction that obtains numeric values from the same sequence being blocked, the nextval operation will never roll back; that is, once a numeric value has been captured, it is considered to have been used, even if the transaction calling nextval exits afterwards. This means that the exited transaction may leave "holes" in the numerical values assigned by the sequence. The setval operation will never roll back.