postgresql 方式插入带有“ON CONFLICT”的行子句语义

发布于 2024-11-05 07:56:32 字数 390 浏览 3 评论 0原文

postgres 中有没有一种简单的方法可以在 sqlite 中执行相当于以下操作的操作？

INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;

我环顾四周，发现的解决方案都是复杂的自定义函数。我的问题的另一个解决方案是只执行

delete from foo where x=1; INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;

语义上不等效但适用于我的情况的操作。

如果不需要自定义函数，我只是更喜欢 ON CONFLICT 规则。

原文

Is there an easy way in postgres to do the equivalent of the following in sqlite?

INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;

I've looked around and the solutions I've found are complicated custom functions. The other solution to my problem is to just do

delete from foo where x=1; INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;

which isn't semantically equivalent but works for my case.

I'd just prefer the ON CONFLICT rule if it doesn't require a custom function.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

叶落知秋 2024-11-12 07:56:32

从 PostgreSQL 版本 9.1（目前为测试版）开始，您可以使用通用表表达式进行插入或替换：

/**
CREATE TABLE foo(id serial primary key, content text unique);
**/

WITH replace AS (
    DELETE FROM foo
    WHERE
        content = 'bar'
    RETURNING content
)
INSERT INTO 
    foo(content) -- values:
SELECT
    *
FROM replace RIGHT JOIN (SELECT CAST('bar' AS text) as content) sub USING(content);

'bar'是将要插入或替换的值。

它不适用于旧版本，您必须等待:-(

As of PostgreSQL version 9.1 (beta at this moment), you can use a common table expression to do an insert-or-replace:

/**
CREATE TABLE foo(id serial primary key, content text unique);
**/

WITH replace AS (
    DELETE FROM foo
    WHERE
        content = 'bar'
    RETURNING content
)
INSERT INTO 
    foo(content) -- values:
SELECT
    *
FROM replace RIGHT JOIN (SELECT CAST('bar' AS text) as content) sub USING(content);

'bar' is the value that will be inserted or replaced.

It's not working in older versions, you have to wait :-(

回复收藏 0 原文