Question

5.4. 序列

-- ------------------------------------------------------
--  'Region'
-- ------------------------------------------------------
DROP TABLE region;
DROP SEQUENCE       region_id_seq;
DROP INDEX              region_id_index;
DROP VIEW vregion;

CREATE TABLE region (
    id                   integer DEFAULT nextval('region_id_seq') NOT NULL,
    region      varchar(20) DEFAULT '' NOT NULL,
    description  text ,
    note        text ,
    remark           text ,
     create_date  timestamp DEFAULT now() ,
     modify_date  timestamp DEFAULT now() ,
    PRIMARY KEY (id),
    UNIQUE (id,region)
);
CREATE SEQUENCE   region_id_seq;
CREATE INDEX          region_id_index ON region (id);

CREATE VIEW vregion AS
    SELECT pv.id,pv.region,pv.description,pv.note,pv.remark,to_char(pv.create_date,'YYYY-MM-DD HH:MI:SS') as date
        FROM region pv
        ORDER BY pv.id;

5.4.1. 等差列 “1，2，3，4，5，6，7，8，9…”

DROP SEQUENCE       region_id_seq;
CREATE SEQUENCE   region_id_seq;

member=> insert into region(region) values('广西');
INSERT 111264 1
member=>
member=> insert into region(region) values('贵州');
INSERT 111265 1
member=>
member=> insert into region(region) values('海南');
INSERT 111266 1
member=>
member=> insert into region(region) values('河北');
INSERT 111267 1
member=>
member=> insert into region(region) values('河南');
INSERT 111268 1
member=>
member=> insert into region(region) values('黑龙江');
INSERT 111269 1
member=> select * from vregion ;
 id | region | description | note | remark |        date
----+--------+-------------+------+--------+---------------------
  1 | 安徽   |             |      |        | 2003-11-01 10:44:26
  2 | 北京   |             |      |        | 2003-11-01 10:44:26
  3 | 重庆   |             |      |        | 2003-11-01 10:44:26
  4 | 福建   |             |      |        | 2003-11-01 10:44:26
  5 | 甘肃   |             |      |        | 2003-11-01 10:44:26
  6 | 广东   |             |      |        | 2003-11-01 10:44:26
  7 | 广西   |             |      |        | 2003-11-01 10:44:26
  8 | 贵州   |             |      |        | 2003-11-01 10:44:26
  9 | 海南   |             |      |        | 2003-11-01 10:44:26
 10 | 河北   |             |      |        | 2003-11-01 10:44:26
 11 | 河南   |             |      |        | 2003-11-01 10:44:26
 12 | 黑龙江 |             |      |        | 2003-11-01 10:44:26
(12 rows)

5.4.2. 等差列 “1，3，5，7，9…”

DROP SEQUENCE       region_id_seq;
Delete from region;
CREATE SEQUENCE region_id_seq INCREMENT 2 START 1;

member=> DROP SEQUENCE region_id_seq;
DROP SEQUENCE
member=> Delete from region;
DELETE 15
member=>
member=> CREATE SEQUENCE region_id_seq INCREMENT 2 START 1;
CREATE SEQUENCE
member=> insert into region(region) values('广东');
INSERT 111282 1
member=>
member=> insert into region(region) values('广西');
INSERT 111283 1
member=>
member=> insert into region(region) values('贵州');
INSERT 111284 1
member=>
member=> insert into region(region) values('海南');
INSERT 111285 1
member=>
member=> insert into region(region) values('河北');
INSERT 111286 1
member=>
member=> insert into region(region) values('河南');
INSERT 111287 1
member=>
member=> insert into region(region) values('黑龙江');
INSERT 111288 1
member=> select * from region ;
 id | region | description | note | remark |        create_date         |        modify_date
----+--------+-------------+------+--------+----------------------------+----------------------------
  1 | 安徽   |             |      |        | 2003-11-01 11:49:58.004475 | 2003-11-01 11:49:58.004475
  3 | 北京   |             |      |        | 2003-11-01 11:49:58.093188 | 2003-11-01 11:49:58.093188
  5 | 重庆   |             |      |        | 2003-11-01 11:49:58.138582 | 2003-11-01 11:49:58.138582
  7 | 福建   |             |      |        | 2003-11-01 11:49:58.166903 | 2003-11-01 11:49:58.166903
  9 | 甘肃   |             |      |        | 2003-11-01 11:49:58.195132 | 2003-11-01 11:49:58.195132
 11 | 广东   |             |      |        | 2003-11-01 11:49:58.239133 | 2003-11-01 11:49:58.239133
 13 | 广西   |             |      |        | 2003-11-01 11:49:58.267372 | 2003-11-01 11:49:58.267372
 15 | 贵州   |             |      |        | 2003-11-01 11:49:58.295643 | 2003-11-01 11:49:58.295643
 17 | 海南   |             |      |        | 2003-11-01 11:49:58.324202 | 2003-11-01 11:49:58.324202
 19 | 河北   |             |      |        | 2003-11-01 11:49:58.352543 | 2003-11-01 11:49:58.352543
 21 | 河南   |             |      |        | 2003-11-01 11:49:58.381273 | 2003-11-01 11:49:58.381273
 23 | 黑龙江 |             |      |        | 2003-11-01 11:49:58.415112 | 2003-11-01 11:49:58.415112
(12 rows)

5.4.3. 等差列 “2，4，6，8，10…”

DROP SEQUENCE       region_id_seq;
Delete from region;
CREATE SEQUENCE region_id_seq INCREMENT 2 START 2;
member=> DROP SEQUENCE region_id_seq;
ERROR:  sequence "region_id_seq" does not exist
member=> Delete from region;
DELETE 0
member=> CREATE SEQUENCE region_id_seq INCREMENT 2 START 2;
CREATE SEQUENCE
member=> insert into region(region) values('安徽');
INSERT 111303 1
member=> insert into region(region) values('北京');
INSERT 111304 1
……
……
member=> insert into region(region) values('海南');
INSERT 111311 1
member=> insert into region(region) values('河北');
INSERT 111312 1
member=> select * from vregion;
 id | region | description | note | remark |        date
----+--------+-------------+------+--------+---------------------
  2 | 安徽   |             |      |        | 2003-11-01 12:00:28
  4 | 北京   |             |      |        | 2003-11-01 12:00:28
  6 | 重庆   |             |      |        | 2003-11-01 12:00:28
  8 | 福建   |             |      |        | 2003-11-01 12:00:28
 10 | 甘肃   |             |      |        | 2003-11-01 12:00:28
 12 | 广东   |             |      |        | 2003-11-01 12:00:28
 14 | 广西   |             |      |        | 2003-11-01 12:00:28
 16 | 贵州   |             |      |        | 2003-11-01 12:00:28
 18 | 海南   |             |      |        | 2003-11-01 12:00:28
 20 | 河北   |             |      |        | 2003-11-01 12:00:28
(10 rows)

5.4.4. n1+n2

CREATE SEQUENCE region_id_seq INCREMENT n2 START n1;