postgres - sequence id 简单使用

参考：http://blog.chinaunix.net/uid-24774106-id-3973781.html

使用命令进入postgres：

sudo -u postgres -i 

进入数据库： psql

查看用户：

查看数据库：\l

使用数据库：\c dbname

使用表：

（创建sequence id 的时候，注意用户需要统一，如果是admin创建的数据库， 那么postgres不能进行操作，可以创建一个表，然后继续操作，把用户进行统一）

 sequence 是一种特殊的数据库对象，用来产生独一无二的数字ID。Postgres中有一种数据类型serial，和sequence对应：

如果我们插入一笔记录，我们希望系统自动为我们分配一个ID，那么我们需要使用serial type。如果我们创建表的时候，自动创建一个serial类型（包smallserial and bigserial），系统会自动帮我们创建sequence这种数据库对象。

1. create table employ(

2.       id serial,

3.       name varchar(64),

4.       department varchar(64)

5. ) ;

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

2.  

3. manu_db-# \d employ<br />
   &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Table &quot;public.employ&quot;<br />
   &nbsp; &nbsp;Column &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Type &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Modifiers &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br />
   ------------+------------------------+-----------------------------------------------------<br />
   &nbsp;id &nbsp; &nbsp; &nbsp; &nbsp; | integer &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| not null default nextval(&#39;employ_id_seq&#39;::regclass)<br />
   &nbsp;name &nbsp; &nbsp; &nbsp; | character varying(64) &nbsp;|&nbsp;<br />
   &nbsp;department | character varying(128) |&nbsp;

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

1. CREATE TABLE employ (
2.       id serial,
3.       name varchar(64),
4.       department varchar(64)
5. ) ;
6.  
7. -----------------------------------------------------------------
8. CREATE SEQUENCE employ_id_seq ;
9. CREATE TABLE employ (
10.      id integer NOT NULL DEFAULT nextval('employ_id_seq'),
11.      name varchar(64),
12.      department varchar(64)
13. );
14.  
15. ALTER SEQUENCE employ_id_seq OWNED by employ.id;

 我们看到，我们用了integer数据类型和serial对应起来，默认用nextal（‘employ_id_seq’）给integer类型的id赋值，那是因为integer范围要比serial的范围大，这样做是安全的。对于bigserial，我们应该用bigint类型，对于smallserial应该何smallint对应。

我们建立数据表和sequence的对应关系，常规的就是这两种方法，要么创建表的时候，某字段是serial类型，要么创建先创建sequence，然后创建表的时候，将某字段和sequence 联系起来（DEFAULT nextvl（‘XXXX_seq’））

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


manu_db=# \d employ
                                   Table "public.employ"
   Column   |          Type          |                      Modifiers                      
------------+------------------------+-----------------------------------------------------
 id         | integer                | not null default nextval('employ_id_seq'::regclass)
 name       | character varying(64)  | 
 department | character varying(128) | 


manu_db=# 
 

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

1. CREATE SEQUENCE employ_copy_id_seq ;
2. ALTER TABLE employ_copy ALTER COLUMN id SET DEFAULT nextval('employ_copy_id_seq');
3. ALTER SEQUENCE employ_copy_id_seq  OWNED by employ_copy.id ;

看看效果

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

注意，加了alter sequence owned by ,在删除table的时候，会自动的将sequence删掉。如果不建立这种owned by的关系，删除table，不会引发sequence被删除。我就不贴了，可以自己try。

    当我们向Postgres插入一笔记录的时候，我们不需要费力告诉DB id的值，因为DB会自动分配一个数值给id：

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

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

1. manu_db=# select currval('employ_id_seq');
2.  currval
3. ---------
4.        2
5. (1 row)
6.  
7. manu_db=# select nextval('employ_id_seq');
8.  nextval
9. ---------
10.        3
11. (1 row)
12.  
13. manu_db=# select nextval('employ_id_seq');
14.  nextval
15. ---------
16.        4
17. (1 row)

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

1. manu_db=# select setval('employ_id_seq',100);
2.  setval
3. --------
4.     100
5. (1 row)
6.  
7. manu_db=# select currval('employ_id_seq');
8.  currval
9. ---------
10.      100
11. (1 row)
12.  
13. manu_db=# select nextval('employ_id_seq');
14.  nextval
15. ---------
16.      101
17. (1 row)