PostgreSQL学习篇9.4 序列类型

最新推荐文章于 2025-05-12 21:31:54 发布

原创最新推荐文章于 2025-05-12 21:31:54 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#PG序列类型

Oracle DBA的PostgreSQL之路同时被 2 个专栏收录

84 篇文章

订阅专栏

postgresql

60 篇文章

订阅专栏

本文介绍了PostgreSQL中的序列类型serial及bigserial的使用方法，并通过创建表、插入数据、查询序列等操作进行了实例演示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

序列类型：serial、bigserial。PG与Oracle一样有序列，但是MySQL没有。

示例：
postgres=# create table testseq(id serial,name varchar(100));
CREATE TABLE
postgres=# insert into testseq(name) values('dxmy');
INSERT 0 1
postgres=# select * from testseq;
 id | name
----+------
  1 | dxmy
(1 row)

postgres=# \d testseq;
                                 Table "public.testseq"
 Column |          Type          |                      Modifiers                       
--------+------------------------+------------------------------------------------------
 id     | integer                | not null default nextval('testseq_id_seq'::regclass)
 name   | character varying(100) |

postgres=#

--看下PG中的sequence
postgres=# create sequence seq_1;
CREATE SEQUENCE
postgres=# \d seq_1;
            Sequence "public.seq_1"
    Column     |  Type   |        Value       
---------------+---------+---------------------
 sequence_name | name    | seq_1
 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
                       ^
postgres=# select currval('seq_1');
ERROR:  currval of sequence "seq_1" is not yet defined in this session
postgres=# select nextval('seq_1');
 nextval
---------
       1
(1 row)

postgres=# select nextval('seq_1');
 nextval
---------
       2
(1 row)

postgres=# select currval('seq_1');
 currval
---------
       2
(1 row)
postgres=# select * from seq_1;    --这个就比较神奇了
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 seq_1         |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t
(1 row)

postgres=# select setval('seq_1',300);    --可以直接设置sequence的值
 setval
--------
    300
(1 row)

postgres=# select currval('seq_1');
 currval
---------
     300
(1 row)

postgres=#