postgresql auto_increment 实现 通用方法

pgsql根oracle在设置auto_increment非常的像，通过设置类似计数器的方法来实现的。感觉pgsql既有oracle部分特点，又有mysql的部分特点。下面详细说一下，auto_increment设置过程，下面的方法具有通用性，也就是说，不管这个是空表，还是已经有数据了，都可以通过该方法，实现auto_increament

bash-3.2$ psql -U playboy -d playboy        //登录
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

playboy=>
playboy=> create table test(id integer,name varchar(32),constraint playboy_id_pk primary key(id));   //测试表
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "playboy_id_pk" for table "test"
CREATE TABLE
playboy=>
playboy=> CREATE SEQUENCE seq_test_id;  //创建该的计数器
CREATE SEQUENCE
playboy=> \d test               //查看一下表
            Table "public.test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(32) | 

Indexes:
    "playboy_id_pk" PRIMARY KEY, btree (id)
playboy=>
playboy=> SELECT setval('seq_test_id', max(id)) FROM test;  //把表中id最大的那个值，付值给计数器
 setval
--------

(1 row)
playboy=>
playboy=> ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('seq_test_id');  //设置id的值，从计数器获取
ALTER TABLE
playboy=>
playboy=> insert into test(name)values('tank');     //插入一条数据
INSERT 0 1
playboy=> select * from test;      //查看一下
 id | name
----+------
  1 | tank
(1 row)
playboy=>
playboy=> insert into test(name)values('zhangying');  //在插入一条
INSERT 0 1
playboy=> select * from test;      //在查看一下
 id |   name
----+-----------
  1 | tank
  2 | zhangying
(2 rows)

有图有真像：

pgsql autocreatment 实现