postgresql auto_increment 实现 通用方法

张映 发表于 2013-05-31

分类目录: pgsql

标签:, , ,

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 实现

pgsql autocreatment 实现



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/pgsql/1526.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   4X8=?