三味线
三味线
Published on 2020-01-14 / 41 Visits
0
0

PGSQL UPSERT使用

PG 9.5开始支持的特性,根据约束,存在则更新,不存在则插入;

使用某字段约束:

INSERT INTO distributors (did, dname) VALUES (8, 'Anvil Distribution')
ON CONFLICT (did) DO UPDATE
SET dname = EXCLUDED.dname

注意字段属性必须是UNIQUE,不然会报错:

there is no unique or exclusion constraint matching the ON CONFLICT specification

使用唯一键约束:

INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING


Comment