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    |           |          |