postgresql数据库中update使用的坑

简介

        在数据库中进行增删改查比较常见，经常会用到update的使用。但是在近期发现update在oracle和postgresql使用却有一些隐形区别，oracle 在执行update语句的时候set 后面必须跟着1对1的数据关联而postgresql数据库却可以一对多，这就导致数据在被新的时候出现不确定性。

目录

简介

案列

oracle

postgresql

解读

postgresql官网解释

update语法分享

update对分区表使用的影响

案列

oracle

以下oracle数据库中的测试代码

 drop table test1;
 create table test1 
 (
     id number ,
     var varchar2(2) 
 ) ;


insert into test1 values(1,2);
select * from test1;

update test1 t
   set var =
       (select tt.var
          from (select 1 id, '3' var
                  from dual
                union all
                select 1, '4'
                  from dual) tt
         where tt.id = t.id);
         
select * from test1;       

postgresql

以下是postgresql数据库中的测试代码

 
drop table if exists test1;
 create table test1 
 (
     id int ,
     var varchar 
 ) ;

select * from test1;

insert into test1 values(1,2);

update test1 t set var=tt.var from (select 1,generate_series(3,4)::varchar ) as tt(id,var) where  tt.id = t.id;

select * from test1;

解读

在两个数据库中都是新建了一张表，其数据只有（1，2），其中id:1是作为set的匹配字段

       

替换新数据表中,其ID为1的值有两个，在set进行匹配的时候就会发生一对多（数据发散）的情况，此时oracle数据库就会报错

select 1 id, '3' var
                  from dual
                union all
                select 1, '4'
                  from dual

一对多（数据发散）的情况，此时oracle数据库就会报错。在oracle数据库中是并不允许的。

但是在postgresql数据库中却可以执行，并且主表数据库被成功更新成了4.

postgresql官网解释

在官网中，postgresql是支持set后的一对多匹配的，但是具体更新成哪一条数据却是不可控的。

注：在日常使用postgresql数据库的时候，注意update语句在使用中，尽可能有唯一主键进行关联。
        其SET后匹配的数据尽可能保证一对一的关联。

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 ] [, ...] ]

         此处的RECURSIVE，递归语法语法的一个关键字，可以对临时表的数据进行聚合算法。

update对分区表使用的影响

        在查询这个一对多进行update执行的时候，官网还对其分区表的分区键进行update后，会不会变更其分区进行了解读：

        在分区表的情况下，更新一行有可能导致它不再满足其所在分区的分区约束。此时，如果这个行满足分区树中某个其他分区的分区约束，那么这个行会被移动到那个分区。 如果没有这样的分区，则会发生错误。在后台，行的移动实际上是一次DELETE操作和一次INSERT操作。

        在移动的行上的并发UPDATE或DELETE可能会收到序列化失败错误。 假设会话 1 正在分区键上执行UPDATE，同时，对可访问该行的并发会话 2 在此行上执行UPDATE或DELETE操作。 在这种情况下，会话 2 的UPDATE 或 DELETE将检测行移动并引发序列化失败错误（该错误始终返回 SQLSTATE 代码"40001"）。 如果发生这种情况，应用程序可能希望重试事务。 在通常情况下，表没有分区或没有行移动，会话 2 将标识新更新的行，并执行UPDATE/DELETE在此新行版本中。

        请注意，虽然行可以从本地分区移动到外表分区（如果外数据包装器支持元组路由），但它们不能从外表分区移动到另一个分区。