PostgreSQL技术问答26 - Manipulation

站长

2024年07月29日 02:10 · 阅读数 25

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文讨论的内容是PostgreSQL中的数据操作(Manipulation)。

什么是Manipulation

Manipulation，英文原意是操纵，操作的意思。在数据库技术中，这个名词的意思就是所有会涉及到数据和信息内容变更的操作。

从逻辑上而言，在最低的层面上，数据操作只有两种类型：Insert(插入)和Delete(删除)，但为了开发和使用方便，增加了Update(更新)，虽然在逻辑上，更新操作可以分解称为删除旧数据和增加新数据两个操作，但显然，形式上使用单一的数据更新操作，更加直观和方便。

在这个基础之上，一些数据操作的更高级复杂的形式，如Upsert或者Merge，其实也是以基本操作作为基础的。比如Upsert实际上是通过冲突条件来判断是进行增加或者修改的操作。设计这些机制的原因，笔者理解还是为了简化操作和提高效率，这样可以在同一个语句中就实现了逻辑判断后操作的流程，并且可以针对批量数据。

和数据操作相对的，就是数据查询(Query)了。它的基本概念就是无论怎样进行查询，它不会对数据本身和数据定义的结构进行修改。当然，如果一个复合性的语句如With...Update，或者先查询后修改的语句块，我们还是应当把它们当成数据操作语句来看待，因为它们最终有对数据进行了实质性的修改。

数据操作，结合数据查询，就构成了关系型数据库和SQL语言中，有关数据相关的所有操作的形式和内容。其中，涉及数据查询相关的语句和功能，也被称为DML(Data Query Language，数据查询语言)；涉及数据修改的语句和功能，被称为(Data Manipulation Language，数据操作语言)，它们都是SQL语言中的重要组成部分。而完整的SQL语言，还包括如DDL(Data Define Language，数据定义语言)、DTL(Data Tranaction Language，数据实务语言)、DCL(Data Control Language，数据控制语言)、CCL(Cursor Control Language，游标控制语言)等等，笔者有机会另行撰文讨论。

DQL和DML的这些操作范式，还可以进一步外化成为所有软件的通用性操作，就是我们熟悉的CRDU(Create-Delete-Update-Read，增删改查)，也是所有信息和应用系统都在做的事情，而且能够在逻辑上构成一个完整的数据和信息的生命周期。

PG如何进行数据操作

在Postgre中(实际上所有的关系型数据库都是如此)，使用对应的三个SQL语句，来进行数据操作，就是Insert、Delete和Update。

Insert插入语句

Insert插入语句，用于将新记录插入到数据库表中。它的完整形式是：

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

使用Insert Into关键字引领插入操作
要指定插入的表
如果不能完整的提供所有新行中，对应字段的值，则需要指定可能要插入哪些字段的值，就是提供一个字段列表
可选Default Values，来插入所有默认值
或者使用Values表函数来设置插入对应字段的值，按照字段顺序，使用括号包围
Values可以指定多个行的数据，使用逗号分隔
也可以使用query作为插入值(我们后面详细讨论)
On Conflict来处理主键和约束冲突的情况
可以使用Returning，指定插入操作后，影响到的结果行
Insert操作的标准输出，是插入操作影响的行的数量，除非使用了Returning

Update更新语句

Update更新语句，用于更新数据库中的记录。它的完整形式是：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

这里的要点如下：

更新语句使用Update作为关键字引领
需要指定要更新的表
更新表中字段的内容，使用 “set 字段名 = 值”的方式
还有一种设置值的形式是 (c1,c2,c3...) = (v1,v2,v3...)，这个对开发比较友好
要更新多个字段的值，使用逗号分隔字段名和值对
更新的值，可以来自常数、表达式、记录集(其他表或CTE)和子查询
如果引用了其他的表或者记录集，需要使用From关键字指定
通过Where子句，指定和过滤要更新内容的行
如果引用了其他的表或者记录集，Where中一般需要加入关联条件
可以使用Returning，指定更新操作后，影响到的结果行
Update操作的标准输出，是更新操作影响的行的数量，除非使用了Returning

Delete删除语句

Postgres使用Delete语句进行数据表中，删除记录行的操作。它的完整形式如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

这里的要点如下：

使用Delete From 作为关键字引导记录删除语句
必须指定要删除记录的表
不需要指定字段，因为删除的是整行
使用Where指定删除条件
如果删除的条件需要关联其他表，需要在Where之前，使用Using指定关联的表或者记录集
可以使用Returning，指定删除操作后，影响到的结果行
Delete操作的标准输出，是删除操作影响的行的数量，除非使用了Returning

批量操作

笔者觉得，要更好的理解和用好SQL语言，首先需要理解的一个重要概念，就是和普通的编程语言相比，SQL的指令都是针对“数据集”的。也就是说，SQL语言天生就是批量操作指令语言。所以，每个在形式上单一的SQL语句，其操作的都是多个数据，它们的组织逻辑就是某种操作条件，而非具体的数据条目（如内存或者磁盘寻址）。

这种设计虽然很强大，而且完全从逻辑概念出发。但在实际操作过程中，也可能出现一些问题，就是对操作范围的不可确定性。当然，这可以通过一些业务规则来进行限制和改善，比如所有操作都应该加入限定条件。这显然对开发者和业务实现的设计者，提出了更高的要求。

在这方面，最典型的一个设计，就是对于数据记录主键的使用，它可以确定操作是被限定在主键所在的数据记录上，确定只对这条特定的记录有效。

举一些基本操作的例子

下面是一些简单(标准)的例子：

// 插入数据
insert into users(id,name) values (1001, 'Tom');

// 一次插入多条数据
inser into users(id,name) values (1002, 'John'), (1003, 'Mary');

// 更新数据
update users set name = 'Jerry' where id = 1001;

// 删除数据
delete from users where id = 1001;

上面的例子非常简单，但也基本体现了数据操作的基本规则和用法，但现实情况中，我们可能会遇到更复杂的情况，特别是可能要更新的数据，并不是简单的常量参数，而是来自其他的表，这才是更常见的情况。我们下面就会展开讨论。

数据操作时，如何引用已有的数据

我们还是分为插入、更新、删除三种情况来展开讨论。

数据插入

除了直接在插入语句中，设置数据参数之外，还有一种常见的情况，就是，这些数据并不完全来自外部，而是来自数据库内部的查询或者计算。下面是一个简单的示例(常用于备份数据)：

-- 复制所有字段
insert into students_bak select *, 100 as flag from students; 

-- 复制特定字段
insert into students_bak (id,name) select id, name from students;

Postgres没有select ... into 语句，而是使用insert into ... select 的模式。逻辑上，任何表表达式都是可以使用的。主要限制在于插入和接收的表结构字段顺序和类型是需要匹配的。

可以看到，上面这种操作方式，需要先预先创建一个类似的表结构。Postgres也提供了相关的操作，可以简单的进行表结构和数据的复制：

-- 只复制表结构
CREATE TABLE adata2 (LIKE adata);
CREATE TABLE

-- 复制表数据
insert into adata2 select * from adata;
INSERT 0 2

-- 复制表结构和数据
CREATE TABLE adata3 AS SELECT * FROM adata;
SELECT 2

可以看到，在PG中，将查询结果插入另一个表，是非常灵活和方便的。我们也可以在中间加入更多的控制逻辑，比如过滤和限制数据，进行字段计算，甚至加入on conflict逻辑进行插入和更新控制等等。

数据更新

在很多需要进行数据更新操作的场景中，虽然可能更新记录的依据是其主键，但如何获取这些主键，却可能基于在另外一个表中的进行的数据关联查询。

一般的做法是先做条件查询，获得操作记录的主键，然后执行基于这些主键进行更新操作。这种做法，通常需要外部的程序或者存储过程来配合，才能完成这两个业务阶段。但实际上，基于关联的更新操作，在Postres中，也是可以直接实现的，借助From句法，可以在单一Update语句中实现。下面是一个简单的例子：

// 原表内容
select * from jusers;
  id  |                       data                        
------+---------------------------------------------------
 1002 | {"name": "Tom", "gender": 1}
 1001 | {"age": 30, "name": "John", "address": "ChengDu"}
(2 rows)

// 关联和条件更新
with ustatus(id, status) as (values (1001, 2), (1002, 3)) 
update jusers set data = data || '{ "status": 1}' from ustatus 
where ustatus.id = jusers.id and (ustatus.status & 3) = 2 returning *;
  id  |                              data                              |  id  | status 
------+----------------------------------------------------------------+------+--------
 1001 | {"age": 30, "name": "John", "status": 1, "address": "ChengDu"} | 1001 |      2
(1 row)

UPDATE 1

这个例子中，我们构造了一个虚拟的用户状态表(当然也可以使用实际的表)。然后在更新用户信息的时候，使用From声明要关联使用这个表，并且在后续的查询条件中，设置了关联方式(id)和过滤方式(状态条件)。这个更新语句，就可以依照一般外部表和关联条件，来执行记录的更新了。

如果熟悉SQL的关联操作，我们可以更好的理解，其实在这个里面from句法的结构和作用，和select语句中，join ... on 其实是一样的。它先将两个表关联起来，然后进行检索，找到满足条件的记录，然后基于这个结果，更新映射的原表中的记录。甚至我们还可以在返回的结果中，看到这两个表，其实是关联在一起进行操作的。

关联更新的操作好处其实还有一个，就是可能不需要一定限定在主键，任何可关联的条件理论上都是可以的，这样就极大的扩展了应用的场景和灵活性。

数据删除

数据删除的操作，和数据更新的情况是非常类似的。只不过使用的关键字有点差异，在Delete中，使用Using关键字，来指定关联的数据表或者记录集。所以，关联Delete的操作可以写出下面的形式：


// 关联和条件删除
with ustatus(id, status) as (values (1001, 2), (1002, 3)) 
delete from jusers using ustatus where ustatus.id = jusers.id and (ustatus.status & 1) = 1 returning *;
  id  |             data             |  id  | status 
------+------------------------------+------+--------
 1002 | {"name": "Tom", "gender": 1} | 1002 |      3
(1 row)

DELETE 1

如果不使用关联删除，也可以考虑使用主键子查询in的方式，这就是比较传统常规的方式了。比如，上面哪个例子就可以改成下面的形式：


with ustatus(id, status) as (values (1001, 2), (1002, 3)) 
delete from jusers where id in (
select id from ustatus where (ustatus.status & 1) = 1);
DELETE 0

Postgres是否支持Merge

简单的说，现在(2024年)支持了。

在Oracle系统中，有一种Merge语句。直观的意思就是“合并”，也就是说，它可以基于数据的存在性，同时完成“数据插入”和“数据更新”的操作。这是一个非常经典和常见的需求。

本质而言，Merge并不是一个全新的操作，而是一种“复合”操作。它将逻辑判断和操作结合在了一起，先判断记录是否存在，然后决定是否插入、更新、或者放弃。这样可以大大简化过程的开发和实现。

Postgres中，原来是没有原生的Merge语句的，但类似的Insert on conflict 可以实现类似的功能。但在最新的Postgres 15版本中，实现和提高了Merge，并且使用方法和Oracle类似。关于这个问题，笔者另外在本系列中有一篇专门进行了探讨。有兴趣的读者可以移步查阅一下，这里就不赘述了。

小结

本文讨论了在SQL和Postgres中，进行数据修改操作的一般方法。从比较高的逻辑层面上，所有的数据库中记录的操作，都可以分为插入、更新和删除这三种类型，它们也在SQL中，对应了Insert、Update和Delete三个语句。文中展示了这几个语句的结构和使用方式，特别重点讨论了使用数据关联结合数据操作的形式，并提出了相关的示例代码。

转载自:https://juejin.cn/post/7392775201094811659