PgSQL 批量查询结果插入或更新 insert into table_name select * from

CREATE TABLE public.users
(
    com_id      INTEGER,
    user_number INTEGER,
    name        VARCHAR(45),
    score       int,
    PRIMARY KEY (com_id, user_number)
);

-- 查询结果批量插入或者更新
INSERT INTO  public.users
select * from  table_name
ON CONFLICT (com_id,user_number) DO UPDATE
SET (name,score) = (excluded.name,excluded.score);




-- insert批量插入或更新
INSERT INTO  public.users (com_id,user_number,name,score)
VALUES (1,4,'dwd',99),(1,2,'d231d',99)
 ON CONFLICT (com_id,user_number) DO UPDATE
SET (name,score) = (excluded.name,excluded.score);

这样就可以满足按查询结果insert into 插入时后按照主键进行插入或者更新啦。