Ubuntu apache centos mysql 程序员 Python 开源 nginx 微软 HTML5 google java linux php wordpress shell Windows Firefox Android linux命令

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)

延伸阅读

评论