pg 序列

tt=# create SEQUENCE department_id_seq;
CREATE SEQUENCE
Time: 33.621 ms
tt=# \ds+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------------------+----------+----------+-------------+------------+-------------
public | andyxi_id_seq | sequence | postgres | permanent | 8192 bytes |
public | department_id_seq | sequence | postgres | permanent | 8192 bytes |
public | orders_id_seq | sequence | postgres | permanent | 8192 bytes |
(3 rows)

tt=# create table departments(id int,name varchar);
CREATE TABLE
Time: 25.877 ms
tt=# insert into departments values(nextval('department_id_seq'),'marketing');
INSERT 0 1
Time: 6.432 ms
tt=# select * from departments;
id | name
----+-----------
1 | marketing
(1 row)

Time: 0.237 ms
tt=# insert into departments values(nextval('department_id_seq'),'marketing2');
INSERT 0 1
Time: 4.981 ms
tt=# select * from departments;
id | name
----+------------
1 | marketing
2 | marketing2
(2 rows)

Time: 0.201 ms
tt=# select * from departments;
id | name
----+------------
1 | marketing
2 | marketing2
(2 rows)

Time: 0.533 ms
tt=# insert into departments values(nextval('department_id_seq'),'marketing3');
INSERT 0 1
Time: 5.865 ms
tt=# select * from departments;
id | name
----+------------
1 | marketing
2 | marketing2
3 | marketing3
(3 rows)

Time: 0.548 ms
tt=# drop table departments;
DROP TABLE
Time: 10.519 ms
tt=# select * from departments;
ERROR: 42P01: relation "departments" does not exist
LINE 1: select * from departments;
^
LOCATION: parserOpenTable, parse_relation.c:1384
Time: 0.594 ms
tt=# \ds+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------------------+----------+----------+-------------+------------+-------------
public | andyxi_id_seq | sequence | postgres | permanent | 8192 bytes |
public | department_id_seq | sequence | postgres | permanent | 8192 bytes |
public | orders_id_seq | sequence | postgres | permanent | 8192 bytes |
(3 rows)

tt=# \ds+ department_id_seq
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------------------+----------+----------+-------------+------------+-------------
public | department_id_seq | sequence | postgres | permanent | 8192 bytes |
(1 row)

tt=# create table departments(id int,name varchar);
CREATE TABLE
Time: 15.978 ms
tt=# insert into departments values(nextval('department_id_seq'),'marketing1');
INSERT 0 1
Time: 6.584 ms
tt=# insert into departments values(nextval('department_id_seq'),'marketing2');
INSERT 0 1
Time: 14.829 ms
tt=# insert into departments values(nextval('department_id_seq'),'marketing3');
INSERT 0 1
Time: 5.064 ms
tt=# \ds+ department_id_seq
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------------------+----------+----------+-------------+------------+-------------
public | department_id_seq | sequence | postgres | permanent | 8192 bytes |
(1 row)

tt=# select * from departments;
id | name
----+------------
4 | marketing1
5 | marketing2
6 | marketing3
(3 rows)

Time: 0.608 ms

 

drop表后，department_id_seq序列并没有返回从原始值1开始。

#########查department_id_seq序列的下一值

 

tt=# select nextval('department_id_seq');
nextval
---------
7
(1 row)

 

Time: 3.557 ms

########查当前序列department_id_seq的值
tt=# select currval('department_id_seq');
currval
---------
7
(1 row)

 

Time: 0.604 ms

 

######设置department_id_seq的序列的下一个输出值
tt=# select setval('department_id_seq',1);
setval
--------
1
(1 row)

 

Time: 4.790 ms
tt=# select currval('department_id_seq');
currval
---------
1
(1 row)

 

Time: 0.658 ms
tt=# select nextval('department_id_seq');
nextval
---------
2
(1 row)

 

Time: 15.076 ms

 

tt=# alter SEQUENCE department_id_seq restart with 100;
ALTER SEQUENCE
Time: 4.377 ms
tt=# select nextval('department_id_seq');
nextval
---------
100
(1 row)

 

Time: 5.426 ms

 

 参考：https://wiki.postgresql.org/wiki/ALTER_SEQUENCE