插入或更新数据

INSERT INTO 表名(字段列表) 
VALUES(值列表)
ON CONFLICT 目标 行为;

示例

示例数据：

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	email VARCHAR NOT NULL,
	active bool NOT NULL DEFAULT TRUE
);

INSERT INTO 
    customers (name, email)
VALUES 
    ('IBM', '[email protected]'),
    ('Microsoft', '[email protected]'),
    ('Intel', '[email protected]');

示例一： 插入“Microsoft”的联系方式

INSERT INTO customers (name, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

由于已存在 Microsoft，并且 ON CONFLICT 的 行为 是 DO NOTHING，所以不执行使用操作。

INSERT INTO customers (name, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT (name) 
DO NOTHING;

INSERT INTO customers (name, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

EXCLUDED.email 是指当前已存在记录中的 email 字段的值