概述

多表關聯查詢的時候會用到臨時表插入數據，然後再用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 語法 數據庫