PostgreSQL15——DML 语句

包括插入数据的 INSERT 语句、更新数据的UPDATE 语句、删除数据的 DELETE 语句，以及合并数据的 MERGE 语句。

-- 准备数据
CREATE TABLE dept (department_id int NOT NULL,department_name varchar(30) NOT NULL,CONSTRAINT dept_pkey PRIMARY KEY (department_id)
);
CREATE TABLE emp (employee_id int NOT NULL,first_name varchar(20) NULL,last_name varchar(25) NOT NULL,hire_date date not null default current_date,salary numeric(8,2) NULL,manager_id int NULL,department_id int NULL,CONSTRAINT emp_pkey PRIMARY KEY (employee_id),CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES dept(department_id) ON DELETE CASCADE,CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES emp(employee_id)
);

一、插入数据

1.1、插入数据

INSERT 语句的简单形式如下：

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...);

其中，value1 是 column1 的值，value2 是 column2 的值。例如：

INSERT INTO dept(department_id, department_name) VALUES ( 10,
'Administration');SELECT * FROM dept;

如果 VALUES 列表为所有字段都指定了值，并且按照表的字段顺序出现，可以省略表名后的字段列表。因此，我们也可以使用以下插入语句：

INSERT INTO dept VALUES ( 20, 'Marketing');SELECT * FROM dept;

指定字段的值也可以使用 DEFAULT，表示使用定义字段时的默认值；如果没有指定默认值使用 NULL。

1.2、插入多行数据

PostgreSQL中的INSERT 语句支持一次插入多行数据，在VALUES 之后使用逗号进行分隔。例如：

INSERT INTO emp
VALUES (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10),
(201, 'Michael', 'Hartstein', '2020-02-02', 13000.00, NULL, 20),
(202, 'Pat', 'Fay', default, 6000.00, 201, 20);SELECT * FROM emp;

以上语句一次增加了 3 名员工信息，日期可以使用字符形式的字面值（‘2020-01-01’）， default表示使用默认的当前日期。

1.3、复制数据

INSERT INTO SELECT 语句可以将一个查询语句的结果插入表中。例如：

create table emp1 (like emp);INSERT INTO emp1
SELECT * FROM emp
WHERE department_id = 20;SELECT * FROM emp1;

我们首先基于 emp 创建了一个新表 emp1，然后通过查询语句将 emp 中的部分数据复制到emp1 中。

1.4、返回插入的数据

PostgreSQL 对SQL 标准进行了扩展，可以在 INSERT 语句之后使用 RETURNING 返回插入的数据值。例如：

INSERT INTO dept
values (30, 'Purchasing')
RETURNING department_id;

以上语句除了插入一条数据到 dept 表中之外，同时还返回了该数据的 department_id。

二、更新数据

2.1、单表更新

PostgreSQL 使用 UPDATE 语句更新表中已有的数据，基本的语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE conditions;

其中， WHERE 决定了需要更新的数据行，只有满足条件的数据才会更新；如果省略WHERE条件，将会更新表中的所有数据，需要谨慎使用。

以下语句将编号为 200 的员工从原部门调动到 Marketing，并且涨薪 1000：

UPDATE emp
SET salary = salary + 1000, department_id = 20
WHERE employee_id = 200;

2.2、跨表更新

除了以上形式的更新语句之外，PostgreSQL 还支持通过关联其他表中的数据进行更新。以下语句利用 emp 中的数据更新 emp1 表：

UPDATE emp1
SET salary = emp.salary, department_id = emp.department_id, manager_id = emp.manager_id
FROM emp
WHERE emp1.employee_id = emp.employee_id;

使用FROM 子句访问emp 中的数据，并且在WHERE 子句中指定了两个表的关联条件。这种语句与多表连接查询（JOIN）类似，有时候也称为多表连接更新（UPDATE JOIN）。

2.3、返回更新后的数据

PostgreSQL 同样对UPDATE 语句进行了扩展，支持使用RETURNING 返回更新后的数据值。例如：

UPDATE emp
set salary = salary + 1000, department_id = 20
WHERE employee_id = 200
RETURNING first_name, last_name, salary;

三、删除数据

3.1、单表删除

删除数据可以使用 DELETE 语句：

DELETE FROM table_name
WHERE conditions;

同样，只有满足 WHERE 条件的数据才会被删除；如果省略，将会删除表中所有的数据。

以下语句用于删除 emp1 中员工编号为 201 的数据：

DELETE
FROM emp1
WHERE employee_id = 201;

3.2、跨表删除

PostgreSQL 同样支持通过关联其他表进行数据删除。以下语句利用 emp 表删除 emp1 表中的数据：

DELETE
FROM emp1
USING emp
WHERE emp1.employee_id = emp.employee_id;

注意，跨表删除使用 USING 关键字引用其他的表，而不是JOIN。以上语句了 emp1 中员工编号存在于 emp 表中的数据，等价于以下子查询实现：

DELETE
FROM emp1
WHERE emp1.employee_id in (SELECT employee_id FROM emp);

3.3、返回被删除的数据

PostgreSQL 中的 DELETE 语句也可以使用 RETURNING 返回被删除的数据。例如：

-- 先插入一些数据
INSERT INTO emp1
SELECT * FROM emp
WHERE department_id = 20;DELETE
FROM emp1
RETURNING *;

我们先从 emp 复制了一些数据到 emp1 中，然后删除所有数据并且返回这些记录。

四、合并数据

4.1、MERGE语句

SQL 标准中定义了一个用于合并数据的 MERGE 语句，可以基于指定条件执行插入、更新或者删除操作。PostgreSQL 15 实现了MERGE 语句，基本语法如下：

MERGE INTO target
USING source
ON join_condition
{ WHEN MATCHED THEN { UPDATE| DELETE | DO NOTHING } |
WHEN NOT MATCHED THEN { INSERT | DO NOTHING } }
;

• target 是合并操作的目标表；
• source 是合并数据的来源，可以是表名或者查询语句；
• ON子句是判断源数据在目标表中是否存在的条件；
• WHEN MATCHED THEN 分支指定了数据匹配（已经存在）时执行的操作；
• WHEN NOT MATCHED THEN 分支指定了数据不存在时的操作。

我们创建一个示例表 account：

CREATE TABLE account (id INTEGER PRIMARY KEY,balance NUMERIC NOT NULL,status VARCHAR(1) NOT NULL CHECK (status IN ('Y', 'N'))
);

使用以下语句为 account 表新增一条记录：

MERGE INTO account a
USING (VALUES(1, 0, 'Y')) s(id, balance, status)
ON a.id = s.id
WHEN MATCHED THEN
UPDATE SET balance = s.balance, status = s.status
WHEN NOT MATCHED THEN
INSERT (id, balance, status)
VALUES (s.id, s.balance, s.status);

由于id 等于1 的记录不存在，以上语句将会执行 WHEN NOT MATCHED THEN 分支，插入一条新的记录。

接下来我们将插入源数据中的 balance 修改为 100，再次执行 MERGE 语句：

MERGE INTO account a
USING (VALUES(1, 100, 'Y')) s(id, balance, status)
ON a.id = s.id
WHEN MATCHED THEN
UPDATE SET balance = s.balance, status = s.status
WHEN NOT MATCHED THEN
INSERT (id, balance, status)
VALUES (s.id, s.balance, s.status);SELECT * FROM account;

以上语句将会执行 WHEN MATCHED THEN 分支，更新 account 表中id 等于1 的记录。

最后，我们在MERGE 语句中增加一个分支，用于删除数据：

MERGE INTO account a
USING (VALUES(1, 100, 'N')) s(id, balance, status)
ON a.id = s.id
WHEN MATCHED AND s.status = 'N' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET balance = s.balance, status = s.status
WHEN NOT MATCHED THEN
INSERT (id, balance, status)
VALUES (s.id, s.balance, s.status);SELECT * FROM account;

语句中的 WHEN MATCHED AND s.status = ‘N’ THEN 表示如果源数据存在，并且源数据中的状态为 N，则删除目标表中的对应记录。因此，最后的查询语句没有返回结果。

4.2、INSERT ON CONFLICT 语句

对于PostgreSQL 14 以及更早版本，可以通过 INSERT INTO … ON CONFLICT… 实现数据合并的功能。

INSERT INTO table_name(column1, column2, ...)
{VALUES (value1, value2, ...) | SELECT ...}
ON CONFLICT conflict_target conflict_action;

其中，conflict_target 是判断数据是否已经存在的条件：

• ( { index_column_name | ( index_expression ) } ) ，基于某个具有索引的字段或者表达式进
  行判断；
• ON CONSTRAINT constraint_name，基于某个唯一约束进行判断。
• conflict_action 表示冲突时采取的操作：
• DO NOTHING，如果数据已经存在，不做任何操作；
• DO UPDATE SET，如果数据已经存在，更新该数据；可以

使用 WHERE 子句进一步限制需要更新的数据。
这种语句通过为 INSERT 语句增加 ON CONFLICT 选项，组合了 INSERT 和UPDATE 语句的功能，因此也被称为 UPSERT 语句。

emp 表中已经存在编号为 200 的员工，如果我们再次插入该编号将会提示主键冲突：

INSERT INTO emp
values (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10)ERROR:  Key (employee_id)=(200) already exists.duplicate key value violates unique constraint "emp_pkey" ERROR:  duplicate key value violates unique constraint "emp_pkey"
SQL state: 23505
Detail: Key (employee_id)=(200) already exists.

此时，我们可以增加冲突处理，从而避免语句出错：

INSERT INTO emp
values (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10)
on conflict (employee_id)
do nothing;

以上语句基于 employee_id 字段是否重复进行判断，冲突时不做任何处理。

另一种处理冲突的方式就是进行数据更新：

SELECT department_id
FROM emp
WHERE employee_id = 200;

INSERT INTO emp
values (200, 'Jennifer', 'Whalen', '2020-01-01', 4400.00, NULL, 10)
on conflict on constraint emp_pkey
do update
set first_name = EXCLUDED.first_name,
last_name = EXCLUDED.last_name,
hire_date = EXCLUDED.hire_date,
salary = EXCLUDED.salary,
manager_id =EXCLUDED.manager_id,
department_id = EXCLUDED.department_id;SELECT *
FROM emp
WHERE employee_id = 200;

该员工的部门编号在前面被修改为 20；我们通过主键约束 emp_pkey 进行重复数据的判断，然后更新该员工的数据；EXCLUDED 是一个特殊的表，代表了原本应该插入的数据行；最终该员工的部门编号被更新为 10。