深入解读PostgreSQL中的序列及其相关函数的用法_PostgreSQL_

 david=# create table tbl_xulie ( david(# id serial, david(# name text); NOTICE: CREATE TABLE will create implicit sequence "tbl_xulie_id_seq" for serial column "tbl_xulie.id" CREATE TABLE david=# 

 CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table.column | NONE } ] 

 david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1; CREATE SEQUENCE david=# david=# create table tbl_xulie2 ( david(# id int4 not null default nextval('tbl_xulie2_id_seq'), david(# name text); CREATE TABLE david=# 

 david=# \d tbl_xulie Table "public.tbl_xulie" Column | Type | Modifiers --------+---------+-------------------------------------------------------- id | integer | not null default nextval('tbl_xulie_id_seq'::regclass) name | text | david=# \d tbl_xulie2 Table "public.tbl_xulie2" Column | Type | Modifiers --------+---------+--------------------------------------------------------- id | integer | not null default nextval('tbl_xulie2_id_seq'::regclass) name | text | david=# 

 david=# \d tbl_xulie_id_seq 

 Sequence "public.tbl_xulie_id_seq" Column | Type | Value ---------------+---------+--------------------- sequence_name | name | tbl_xulie_id_seq 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 Owned by: public.tbl_xulie.id 

 david=# select * from tbl_xulie2_id_seq; 

 sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called -------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- tbl_xulie2_id_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | f (1 row) 

 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy'); INSERT 0 1 david=# select * from tbl_xulie; 

 id | name ----+------- 1 | David 2 | Sandy (2 rows) 

 david=# truncate tbl_xulie; TRUNCATE TABLE david=# david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Eagle'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Miles'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Simon'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Rock'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Peter'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sally'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Nicole'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Monica'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Renee'); INSERT 0 1 david=# select * from tbl_xulie; 

 id | name ----+-------- 15 | Sandy 16 | David 17 | Eagle 18 | Miles 19 | Simon 20 | Rock 21 | Peter 22 | Sally 23 | Nicole 24 | Monica 25 | Renee (11 rows) 

 david=# copy tbl_xulie to '/tmp/tbl_xulie.sql'; COPY 11 david=# truncate tbl_xulie; TRUNCATE TABLE david=# alter sequence tbl_xulie_id_seq restart with 100; ALTER SEQUENCE david=# select currval('tbl_xulie_id_seq'); currval 

 --------- 25 (1 row) 

 david=# select nextval('tbl_xulie_id_seq'); nextval 

 --------- 100 (1 row) 

 david=# select nextval('tbl_xulie_id_seq'); 

 nextval --------- 101 (1 row) 

 david=# begin; BEGIN david=# copy tbl_xulie from '/tmp/tbl_xulie.sql'; COPY 11 david=# select setval('tbl_xulie_id_seq', max(id)) from tbl_xulie;  setval 

 -------- 25 (1 row) 

 david=# end; COMMIT david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash'); INSERT 0 1 david=# select * from tbl_xulie; 

 id | name ----+-------- 15 | Sandy 16 | David 17 | Eagle 18 | Miles 19 | Simon 20 | Rock 21 | Peter 22 | Sally 23 | Nicole 24 | Monica 25 | Renee 26 | Flash (12 rows) 

 david=# select nextval('tbl_xulie_id_seq'); nextval 

 --------- 27 (1 row) 

 david=# select nextval('tbl_xulie_id_seq'); nextval 

 --------- 3 (1 row) 

 david=# select nextval('tbl_xulie_id_seq'); nextval 

 --------- 4 (1 row) 

 david=# select nextval('tbl_xulie_id_seq'); nextval 

 --------- 4 (1 row) 

 david=# select currval('tbl_xulie_id_seq'); currval 

 --------- 4 (1 row) 

 david=# select currval('tbl_xulie_id_seq'); currval 

 --------- 4 (1 row) 

 a. setval(regclass, bigint) david=# truncate tbl_xulie; TRUNCATE TABLE david=# select setval('tbl_xulie_id_seq', 1); setval 

 -------- 1 (1 row) 

 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David'); INSERT 0 1 david=# select * from tbl_xulie; 

 id | name ----+------- 2 | Sandy 3 | David (2 rows) 

 david=# select currval('tbl_xulie_id_seq'); currval 

 --------- 3 (1 row) 

 david=# select nextval('tbl_xulie_id_seq'); 

 nextval --------- 4 (1 row) 

 b. setval(regclass, bigint, boolean) b.1 setval(regclass, bigint, true) david=# truncate tbl_xulie; TRUNCATE TABLE david=# select setval('tbl_xulie_id_seq', 1, true); setval 

 -------- 1 (1 row) 

 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David'); INSERT 0 1 david=# select * from tbl_xulie; 

 id | name ----+------- 2 | Sandy 3 | David (2 rows) 

 b.2 setval(regclass, bigint, false) david=# truncate tbl_xulie; TRUNCATE TABLE david=# select setval('tbl_xulie_id_seq', 1, false); setval 

 -------- 1 (1 row) 

 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy'); INSERT 0 1 david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David'); INSERT 0 1 david=# select * from tbl_xulie; 

 id | name ----+------- 1 | Sandy 2 | David (2 rows) 

 ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAX
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：Postgresql ALTER语句常用操作小结_PostgreSQL_
                                                                
下一篇：PostgreSQL中常用的时间日期脚本使用教程_PostgreSQL_