PostgreSQL学习篇9.4 序列类型

序列类型：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=#

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!