power query怎么将列内容变为一致（一文看懂PG的WITHQueries语法和UPSERT语法-）
power query怎么将列内容变为一致（一文看懂PG的WITHQueries语法和UPSERT语法-）--建表 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 'xia
概述多表关联查询的时候会用到临时表插入数据，然后再用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进去了。
[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]
2、实验
1)环境准备
--建表
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);
2）with 实现
用t2 这张表去更新t1 ，会将test1中没有的数据插入
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);
二、pg的UPSERT语法
PostgreSQL 9.5 引入了一项新功能，UPSERT(insert on conflict do)，当插入遇到约束错误时，直接返回，或者改为执行UPDATE。
1、语法
[ 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 ]
2、实验
1)环境准备
--建表
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);
2）insert on implict实现
--不存在则插入，存在则更新
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;
到这里关于merge into实现的八种方案已经都介绍完了，觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
