2015-08-02 10:55:22| 分类: Postgres基础 | 标签: |举报 |字号大中小 订阅
fdb=> create table user_logins (username character varying (64) primary key,logins numeric(10,0));
CREATE TABLE
fdb=> insert into user_logins values ('francs',1);
INSERT 0 1
fdb=> insert into user_logins values ('matiler',2);
INSERT 0 1
fdb=> select * from user_logins ;
username | logins
----------+--------
francs | 1
matiler | 2
fdb=> INSERT INTO user_logins (username, logins) VALUES ('tutu',1),('francs',1);
ERROR: duplicate key value violates unique constraint "user_logins_pkey"
DETAIL: Key (username)=(francs) already exists.
备注:定义 ON CONFLICT 属性后,已有的用户只需更新 logins 值。EXCLUDED 为试图插入的值。INSERT INTO user_logins (username, logins)
VALUES ('tutu',1),('francs',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
fdb=> select * from user_logins ;
username | logins
----------+--------
matiler | 2
tutu | 1
francs | 2
备注:conflict_target 指冲突类型,例如 unique 约束或用户自定义约束。conflict_action 定义当冲突发生时采取的动作,例如 ON CONFLICT DO NOTHING 和 ON CONFLICT DO UPDATE。[ ON CONFLICT [ conflict_target ] conflict_action ]
fdb=> create table citys(city_name character varying(64) primary key );
CREATE TABLE
fdb=> insert into citys values('Hanzhoug'),('beijing'),('shanghai');
INSERT 0 3
fdb=> select * from citys ;
city_name
-----------
Hanzhoug
beijing
shanghai
(3 rows)
fdb=> insert into citys values('Hanzhoug'),('shenzhen');
ERROR: duplicate key value violates unique constraint "citys_pkey"
DETAIL: Key (city_name)=(Hanzhoug) already exists.
备注:可见没有违反主键约束的数据可以插入,违反了主键约束的数据不能插入,但也不报错,这在一些日志表的使用场很有用,当然上面这个例子并没有指定 conflict_target ,假如表上有多个约束,则应该指定 conflict_target。fdb=> insert into citys values('Hanzhoug'),('shenzhen') on conflict do nothing;
INSERT 0 1
fdb=> select * from citys ;
city_name
-----------
Hanzhoug
beijing
shanghai
shenzhen
(4 rows)
fdb=> insert into citys values('Hanzhoug'),('suzhou') on conflict (city_name) do nothing;
INSERT 0 1
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
ON CONFLICT (did) WHERE is_active DO NOTHING;
评论