几个月前,我从Stack Overflow的答案学习了如何使用以下语法在MysqL中同时执行多个更新:
@H_404_3@我现在切换到Postgresql,显然这是不正确的。它指的是所有正确的表,所以我认为这是一个问题使用不同的关键字,但我不知道在Postgresql文档中包括这里。 @H_404_3@为了澄清,我想插入几个东西,如果他们已经存在更新他们。
- INSERT INTO table (id,field,field2) VALUES (1,A,X),(2,B,Y),(3,C,Z)
- ON DUPLICATE KEY UPDATE field=VALUES(Col1),field2=VALUES(Col2);
Postgresql从9.5版本开始有
UPSERT语法,带有
ON CONFLICT子句。与以下语法(类似于MysqL)
@H_404_3@搜索postgresql的电子邮件组归档“upsert”导致找到an example of doing what you possibly want to do,in the manual:
- INSERT INTO the_table (id,column_1,column_2)
- VALUES (1,'A','X'),'B','Y'),'C','Z')
- ON CONFLICT (id) DO UPDATE
- SET column_1 = excluded.column_1,column_2 = excluded.column_2;
@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:
@H_404_3@可能有一个例子,如何做到这一点批量,使用CTE在9.1及以上,在hackers mailing list:
- CREATE TABLE db (a INT PRIMARY KEY,b TEXT);
- CREATE FUNCTION merge_db(key INT,data TEXT) RETURNS VOID AS
- $$
- BEGIN
- LOOP
- -- first try to update the key
- -- note that "a" must be unique
- UPDATE db SET b = data WHERE a = key;
- IF found THEN
- RETURN;
- END IF;
- -- not there,so try to insert the key
- -- if someone else inserts the same key concurrently,-- we could get a unique-key failure
- BEGIN
- INSERT INTO db(a,b) VALUES (key,data);
- RETURN;
- EXCEPTION WHEN unique_violation THEN
- -- do nothing,and loop to try the UPDATE again
- END;
- END LOOP;
- END;
- $$
- LANGUAGE plpgsql;
- SELECT merge_db(1,'david');
- SELECT merge_db(1,'dennis');
@H_404_3@更清晰的例子见a_horse_with_no_name’s answer。
- WITH foos AS (SELECT (UNNEST(%foo[])).*)
- updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
- INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
- WHERE updated.id IS NULL;