postgresql auto_increment 实现 通用方法,感觉pgsql既有o


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)

相关内容