概述

多表关联查询的时候会用到临时表插入数据，然后再用select查行查询，在往临时表里插入数据的时候，我们经常会用到判断如果临时表里有了这部分数据我们就要更新数据，如果临时表里没有这部分数据我们就要插入，这个时候可以怎么去实现呢？

下面介绍PG数据库的两种另类实现merge into语法方式。

今天也是圣诞节了，在这里祝大家圣诞节快乐，平平安安~

一、PG的WITH Queries语法

PostgreSQL中不直接支持merge into这个语法，但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法实现相同的功能。

1、语法

主要是利用了postgresql的一个update特性—RETURNING，返回一个update的结果集，因为查询条件的存在(也因为它是主键，是唯一)，就会将两张表重叠的部分给过滤出来，再用where not exists将这些重叠的部分给忽略掉，这样就将数据merge进去了。

<code>[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]/<code>

2、实验

1)环境准备

<code>--建表
create  table t1(
id  int primary key,
name text,
sale int,
operatime timestamp
);
create  table t2( 

id  int primary key,
name text,
sale int
);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,now());
INSERT into t1 values(2,'xiaoming',500,now());
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);/<code>

2）with 实现

用t2 这张表去更新t1 ，会将test1中没有的数据插入

<code>WITH upsert AS (
  UPDATE t1  SET sale = t2.sale  FROM t2  WHERE t1.id = t2.id  RETURNING t1.*
 )
INSERT INTO t1 SELECT id,name,sale,now() FROM t2
WHERE NOT EXISTS ( SELECT 1  FROM upsert b  WHERE t2.id = b.id);/<code>

二、pg的UPSERT语法

PostgreSQL 9.5 引入了一项新功能，UPSERT(insert on conflict do)，当插入遇到约束错误时，直接返回，或者改为执行UPDATE。

1、语法

<code>[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    [ ON CONFLICT [ conflict_target ] conflict_action ]  
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
  
where conflict_target can be one of:  
  
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    ON CONSTRAINT constraint_name  
  
and conflict_action is one of:  
  
    DO NOTHING  
    DO UPDATE SET { column_name = { expression | DEFAULT } |  
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ] /<code>

2、实验

1)环境准备

<code>--建表
create  table t1( 

id  int primary key,
name text,
sale int,
operatime timestamp
);
create  table t2(
id  int primary key,
name text,
sale int
);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,now());
INSERT into t1 values(2,'xiaoming',500,now());
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);/<code>

2）insert on implict实现

<code>--不存在则插入，存在则更新
insert into t1 select id,name,sale,now() from t2 on conflict (id) do update set sale=excluded.sale;
--不存在则插入，存在则直接返回(不做任何处理)
insert into t1 select id,name,sale,now() from t2 on conflict (id) do nothing;/<code>

到这里关于merge into实现的八种方案已经都介绍完了，觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

閱讀更多 波波說運維 的文章

關鍵字: PostgreSQL 语法 数据库