PostgreSQL之sequence

转载来自：http://blog.chinaunix.net/uid-24774106-id-3973781.html

sequence 是一种特殊的数据库对象，用来产生独一无二的数字ID。Postgres中有一种数据类型serial，和sequence对应：@H_301_9@ @H_301_9@ 如果我们插入一笔记录，我们希望系统自动为我们分配一个ID，那么我们需要使用serial type。如果我们创建表的时候，自动创建一个serial类型（包smallserial and bigserial），系统会自动帮我们创建sequence这种数据库对象。@H_301_9@

 
  createtableemploy(@H_301_9@ 
 id serial,@H_301_9@ 
 namevarchar(64),@H_301_9@ 
 department)@H_301_9@ 
 ;
 

manu_db=# create table employ(id serial,name varchar(64),department varchar(128)) ;@H_301_9@ NOTICE: CREATE TABLE will create implicit sequence "employ_id_seq" for serial column "employ.id"@H_301_9@ CREATE TABLE@H_301_9@ manu_db=# \d@H_301_9@ List of relations@H_301_9@ Schema | Name | Type | Owner@H_301_9@ --------+---------------+----------+-------@H_301_9@ public | employ | table | manu@H_301_9@ public | employ_id_seq | sequence | manu@H_301_9@ (2 rows)

@H_301_9@

我们看到，postgres自动帮助我们创建了一个sequence，命名原则是tablename_columnname_seq. @H_301_9@ 下面这两条sql，本质是相等： @H_301_9@

CREATETABLEemploy(@H_301_9@

id serial;@H_301_9@

@H_301_9@

-----------------------------------------------------------------@H_301_9@

SEQUENCEemploy_id_seq;@H_301_9@

(@H_301_9@

idintegerNOTNULLDEFAULTnextval('employ_id_seq';@H_301_9@

@H_301_9@

ALTERSEQUENCEemploy_id_seq OWNEDbyemploy.id;

我们看到，我们用了integer数据类型和serial对应起来，默认用nextal（‘employ_id_seq’）给integer类型的id赋值，那是因为integer范围要比serial的范围大，这样做是安全的。对于bigserial，我们应该用bigint类型，对于smallserial应该何smallint对应。 @H_301_9@ @H_301_9@ 我们建立数据表和sequence的对应关系，常规的就是这两种方法，要么创建表的时候，某字段是serial类型，要么创建先创建sequence，然后创建表的时候，将某字段和sequence 联系起来（DEFAULT nextvl（‘XXXX_seq’））,有没有第三种方法？假如创建表的时候，没有用DEFAULT nextval建立起来联系，还有没有机会在后面建立这种联系？我为什么纠结这个？

manu_db=# create table employ_copy (like employ) ;@H_301_9@ CREATE TABLE@H_301_9@ manu_db=# \d employ_copy@H_301_9@ Table "public.employ_copy"@H_301_9@ Column | Type | Modifiers@H_301_9@ ------------+------------------------+-----------@H_301_9@ id | integer | not null@H_301_9@ name | character varying(64) |@H_301_9@ department | character varying(128) |@H_301_9@ @H_301_9@ @H_301_9@ manu_db=# \d employ@H_301_9@ Table "public.employ"@H_301_9@ Column | Type | Modifiers @H_301_9@ ------------+------------------------+-----------------------------------------------------@H_301_9@ id | integer | not null default nextval('employ_id_seq'::regclass)@H_301_9@ name | character varying(64) |@H_301_9@ department | character varying(128) |@H_301_9@ @H_301_9@ @H_301_9@ manu_db=#@H_301_9@ @H_301_9@

如果业务需要，创建一个schema和employ一模一样的table，我们只能够用like，但是，我们看到，id这个字段并不完全一样，原因就是没有sequence和新建的table对应。这种情况，我们有没有办法，事后创建sequence，建立table中id字段的联系， I mean id use nextval as default？ @H_301_9@ 答案是肯定的： @H_301_9@

SEQUENCEemploy_copy_id_seq;@H_301_9@

TABLEemploy_copyCOLUMNidSET'employ_copy_id_seq';

ALTER SEQUENCE employ_copy_id_seq OWNED by employ_copy.id ;

看看效果： @H_301_9@

manu_db=# CREATE SEQUENCE employ_copy_id_seq ;@H_301_9@ CREATE SEQUENCE@H_301_9@ manu_db=# ALTER TABLE employ_copy ALTER COLUMN id SET DEFAULT nextval('employ_copy_id_seq');@H_301_9@ ALTER TABLE@H_301_9@ manu_db=# \d employ_copy@H_301_9@ Table "public.employ_copy"@H_301_9@ Column | Type | Modifiers @H_301_9@ ------------+------------------------+----------------------------------------------------------@H_301_9@ id | integer |not null default nextval('employ_copy_id_seq'::regclass)@H_301_9@ name | character varying(64) |@H_301_9@ department | character varying(128) |@H_301_9@

@H_301_9@

注意，加了alter sequence owned by,在删除table的时候，会自动的将sequence删掉。如果不建立这种owned by的关系，删除table，不会引发sequence被删除。我就不贴了，可以自己try。 @H_301_9@ @H_301_9@ 当我们向Postgres插入一笔记录的时候，我们不需要费力告诉DB id的值，因为DB会自动分配一个数值给id： @H_301_9@

manu_db=# insert into employ (name,department) values('bean','DDI');@H_301_9@ INSERT 0 1@H_301_9@ manu_db=# insert into employ (name,department) values('albert','DDI');@H_301_9@ INSERT 0 1@H_301_9@ manu_db=# select * from employ ;@H_301_9@ id | name | department@H_301_9@ ----+--------+------------@H_301_9@ 1| bean | DDI@H_301_9@ 2| albert | DDI@H_301_9@ (2 rows)@H_301_9@ @H_301_9@

我们可以用currval 和 nextval取到sequence的当前值和下一个值。注意，currval是不会改变sequence的，但是nextval一旦使用，下个数字就被消耗掉了，哪怕你并没有真正的用于你的table。 @H_301_9@

manu_db=#select currval;@H_301_9@

currval@H_301_9@

--@H_301_9@

2@H_301_9@

(1 row)@H_301_9@

@H_301_9@

manu_db#select nextval;@H_301_9@

nextval@H_301_9@

-@H_301_9@

3@H_301_9@

-@H_301_9@

4@H_301_9@

)

使用setval可以设置下一个当前值，下一次就会从你设置的这个值开始递增： @H_301_9@

#select setval;@H_301_9@

setval@H_301_9@

-@H_301_9@

100@H_301_9@

;@H_301_9@

currval@H_301_9@

-@H_301_9@

101@H_301_9@

)

参考文献 1A Tour of Postgresql Data Types 2 Postgres manual 9.1

PostgreSQL之sequence

猜你在找的Postgre SQL相关文章