sql - upsert中文




在PostgreSQL中插入重复更新? (11)

几个月前,我从Stack Overflow的一个答案中学习了如何在MySQL中使用以下语法一次执行多个更新:

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,显然这是不正确的。 它指的是所有正确的表格,所以我认为这是一个使用不同关键字的问题,但我不确定在PostgreSQL文档中涉及的内容。

为了澄清,我想插入几件事情,如果它们已经存在以更新它们。


UPDATE将返回修改的行数。 如果您使用JDBC(Java),则可以将该值与0进行比较,如果没有行受到影响,请替换INSERT。 如果您使用其他编程语言,可能仍然可以获得修改的行数,请检查文档。

这可能不是那么优雅,但你有更简单的SQL,从调用代码中使用更简单。 不同的是,如果您在PL / PSQL中编写了十行脚本,您可能应该为其单独测试一种或另一种类型的脚本。


编辑:这不符合预期。 与接受的答案不同,当两个进程同时重复调用upsert_foo时,会产生唯一的键违规。

找到了! 我想出了一种在一个查询中执行的方法:使用UPDATE ... RETURNING来测试是否有任何行受到影响:

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

UPDATE必须在单独的过程中完成,因为不幸的是,这是一个语法错误:

... WHERE NOT EXISTS (UPDATE ...)

现在它按需要工作:

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');


为了合并小集合,使用上面的函数很好。 但是,如果您合并大量数据,我会建议您浏览http://mbk.projects.postgresql.org

目前我知道的最佳做法是:

  1. 将新的/更新的数据复制到临时表中(当然,如果成本正常,您可以执行INSERT操作)
  2. 获取锁定[可选](建议优于表锁,IMO)
  3. 合并。 (有趣的部分)

在PostgreSQL 9.5及更新版本中,您可以使用INSERT ... ON CONFLICT UPDATE

请参阅文档

MySQL INSERT ... ON DUPLICATE KEY UPDATE可以直接转换为ON CONFLICT UPDATE 。 SQL标准语法都不是,它们都是特定于数据库的扩展。 MERGE没有用于这方面的原因很好 ,一个新的语法不是为了好玩而创建的。 (MySQL的语法也有问题,这意味着它不被直接采用)。

例如给定的设置:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

MySQL查询:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

变为:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

区别:

  • 必须指定用于唯一性检查的列名称(或唯一约束名称)。 这是ON CONFLICT (columnname) DO

  • 必须使用关键字SET ,就像这是一个普通的UPDATE语句一样

它也有一些不错的功能:

  • 你可以在你的UPDATE上有一个WHERE子句(让你有效地将ON CONFLICT UPDATE变成ON CONFLICT IGNORE以获得某些值)

  • 推荐的插入值可用作行变量EXCLUDED ,它与目标表具有相同的结构。 您可以使用表名获取表中的原始值。 所以在这种情况下, EXCLUDED.c将为10 (因为这是我们试图插入的内容), "table".c将为3因为这是表中的当前值。 您可以在SET表达式和WHERE子句中使用其中之一或两者。

有关upsert的背景信息,请参阅PostgreSQL中的如何UPSERT(MERGE,INSERT ... ON DUPLICATE UPDATE)?


就我个人而言,我已经在插入语句中设置了一个“规则”。 假设你有一个“dns”表,每个客户记录dns点击次数:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

您希望能够重新插入具有更新值的行,或者如果它们不存在,则可以创建它们。 键入customer_id和时间。 像这样的东西:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

更新:如果发生同时插入,这可能会失败,因为它会生成unique_violation异常。 但是,未终止的交易将继续并成功,您只需重复终止的交易。

但是,如果有大量的插入操作一直在发生,那么您需要在插入语句中放置一个表锁:SHARE ROW EXCLUSIVE锁定将阻止任何可能会插入,删除或更新目标表中的行的操作。 但是,不更新唯一密钥的更新是安全的,因此如果没有操作将执行此操作,请改用建议锁。

此外,COPY命令不使用RULES,所以如果您使用COPY插入,则需要使用触发器。


我上面定制了“upsert”函数,如果你想插入和替换:

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

执行之后,请执行以下操作:

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

重要的是放置双美元逗号以避免编译器错误

  • 检查速度...

我使用这个函数合并

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

没有简单的命令来做到这一点。

最正确的方法是使用函数,就像docs函数一样。

另一种解决方案(尽管不是那么安全)是使用返回进行更新,检查哪些行是更新,并插入其余的

沿着以下方向的东西:

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

假设id:2被返回:

insert into table (id, column) values (1, 'aa'), (3, 'cc');

当然,它会迟早(在并发环境中)退出,因为这里有明显的竞争条件,但通常会起作用。

这是一篇关于这个话题更长和更全面的文章


管理与名称值对相同的帐户设置时遇到同样的问题。 设计标准是不同的客户可能有不同的设置集。

我的解决方案与JWP类似,是批量擦除和替换,在您的应用程序中生成合并记录。

这非常防弹,独立于平台,并且由于每个客户端的设置从不超过20个,所以这只是3个相当低的负载数据库调用 - 可能是最快的方法。

因为(如上所述)非标准的SQL异常处理从数据库更改为数据库 - 甚至发布到发布,所以更新单独行的替代方法 - 检查异常,然后插入 - 或者某些组合是丑陋的代码。

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT






sql-merge