PG的upsert写法 批量处理写法

create table test_user
(
    id     bigserial,
    user_name   varchar(30) not null,
    gender varchar(30),
    account varchar(30) not null,
    created_time timestamp(6) with time zone,
    primary key(id)
);

-- 新增一条数据
INSERT INTO test_user(user_name, gender, account, created_time) values ('dalao','male','dalao',now());

-- 模拟主键冲突
INSERT INTO test_user(id,user_name, gender, account, created_time) values (1,'dalao','male','dalao',now());

-- 冲突时什么都不做
INSERT INTO test_user(id,user_name, gender, account, created_time) values (1,'dalao','male','dalao',now())
ON CONFLICT(id)
DO NOTHING ;

-- 使用PG的UPSERT 冲突时更新数据
INSERT INTO test_user(id,user_name, gender, account, created_time) values (1,'dalao','male','dalao',now())
ON CONFLICT(id)
DO UPDATE
SET user_name = 'dalao', gender='male2', account='dalao2', created_time = now();

-- mybatis 使用pg的upsert 批量操作 冲突时更新数据
<insert id="upsertBatch" >
    insert into test_user(id,user_name, gender, account, created_time)
    values
    <foreach collection="entities" item="entity" separator=",">
        (#{entity.id},#{entity.userName}, #{entity.gender}, #{entity.account},#{entity.createdTime})
    </foreach>
    on conflict (id) do update set
    user_name = EXCLUDED.user_name , gender = EXCLUDED.gender , account = EXCLUDED.account , created_time = EXCLUDED.created_time 
</insert>