PG 序列相关

获取序列当前值

• select last_value from <seq_name>;

test=# create sequence seq_test_id;
CREATE SEQUENCE
test=# select nextval('seq_test_id');
 nextval
---------
       1
(1 row)

test=# select nextval('seq_test_id');
 nextval
---------
       2
(1 row)

test=# SELECT * from SEQ_test_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          2 |      31 | t
(1 row)
      

• 或者通过如下方式查询

SELECT
    schemaname AS SCHEMA,
    sequencename AS SEQUENCE,
LAST_VALUE 
FROM
    pg_sequences where     sequencename='<sequence_name>';      

获取序列下一个值

test=# select nextval('seq_test_id');
 nextval
---------
       3
(1 row)
      

事务回滚序列不会回滚

test=# select nextval('seq_test_id');
 nextval
---------
       3
(1 row)

test=# select nextval('seq_test_id');
 nextval
---------
       3
(1 row)

test=#  begin;
BEGIN
test=# select nextval('seq_test_id');
 nextval
---------
       4
(1 row)

test=# rollback;
ROLLBACK
test=# SELECT * from SEQ_test_id;
 last_value | log_cnt | is_called
------------+---------+-----------
          4 |      32 | t
(1 row)

test=# select nextval('seq_test_id');
 nextval
---------
       5
(1 row)      

复制表结构时候序列会共用, 这个需要注意

test=# create table t1 (id serial, info text);
CREATE TABLE
test=# \d t1
                            Table "public.t1"
 Column |  Type   | Collation | Nullable |            Default
--------+---------+-----------+----------+--------------------------------
 id     | integer |           | not null | nextval('t1_id_seq'::regclass)
 info   | text    |           |          |

test=#
test=# create table t2 (like t1 including all);
CREATE TABLE
test=# \d t2
                            Table "public.t2"
 Column |  Type   | Collation | Nullable |            Default
--------+---------+-----------+----------+--------------------------------
 id     | integer |           | not null | nextval('t1_id_seq'::regclass)
 info   | text    |           |          |