插入,在重复更新在PostgreSQL?

前端之家收集整理的这篇文章主要介绍了插入,在重复更新在PostgreSQL?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
几个月前,我从Stack Overflow的答案学习了如何使用以下语法在MysqL中同时执行多个更新:
  1. INSERT INTO table (id,field,field2) VALUES (1,A,X),(2,B,Y),(3,C,Z)
  2. ON DUPLICATE KEY UPDATE field=VALUES(Col1),field2=VALUES(Col2);
@H_404_3@我现在切换到Postgresql,显然这是不正确的。它指的是所有正确的表,所以我认为这是一个问题使用不同的关键字,但我不知道在Postgresql文档中包括这里。

@H_404_3@为了澄清,我想插入几个东西,如果他们已经存在更新他们。

Postgresql从9.5版本开始有 UPSERT语法,带有 ON CONFLICT子句。与以下语法(类似于MysqL)
  1. INSERT INTO the_table (id,column_1,column_2)
  2. VALUES (1,'A','X'),'B','Y'),'C','Z')
  3. ON CONFLICT (id) DO UPDATE
  4. SET column_1 = excluded.column_1,column_2 = excluded.column_2;
@H_404_3@搜索postgresql的电子邮件组归档“upsert”导致找到an example of doing what you possibly want to do,in the manual

@H_404_3@Example 38-2. Exceptions with UPDATE/INSERT

@H_404_3@This example uses exception handling to perform either UPDATE or INSERT,as appropriate:

  1. CREATE TABLE db (a INT PRIMARY KEY,b TEXT);
  2.  
  3. CREATE FUNCTION merge_db(key INT,data TEXT) RETURNS VOID AS
  4. $$
  5. BEGIN
  6. LOOP
  7. -- first try to update the key
  8. -- note that "a" must be unique
  9. UPDATE db SET b = data WHERE a = key;
  10. IF found THEN
  11. RETURN;
  12. END IF;
  13. -- not there,so try to insert the key
  14. -- if someone else inserts the same key concurrently,-- we could get a unique-key failure
  15. BEGIN
  16. INSERT INTO db(a,b) VALUES (key,data);
  17. RETURN;
  18. EXCEPTION WHEN unique_violation THEN
  19. -- do nothing,and loop to try the UPDATE again
  20. END;
  21. END LOOP;
  22. END;
  23. $$
  24. LANGUAGE plpgsql;
  25.  
  26. SELECT merge_db(1,'david');
  27. SELECT merge_db(1,'dennis');
@H_404_3@可能有一个例子,如何做到这一点批量,使用CTE在9.1及以上,在hackers mailing list

  1. WITH foos AS (SELECT (UNNEST(%foo[])).*)
  2. updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
  3. INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
  4. WHERE updated.id IS NULL;
@H_404_3@更清晰的例子见a_horse_with_no_name’s answer

猜你在找的Postgre SQL相关文章