PostgreSQL插入时冲突更新（upsert）使用所有排除的值

Question

当你正在执行一次 upsert 行（PostgreSQL >= 9.5）时，如果你希望可能的 INSERT 与可能的 UPDATE 完全相同，你可以按照以下方式编写它：

当你正在执行一次upsert行（PostgreSQL >= 9.5）时，如果你希望可能的INSERT与可能的UPDATE完全相同，你可以这样写：

INSERT INTO tablename (id, username, password, level, email) 
                VALUES (1, 'John', 'qwerty', 5, '[e-mail]') 
ON CONFLICT (id) DO UPDATE SET 
  id=EXCLUDED.id, username=EXCLUDED.username,
  password=EXCLUDED.password, level=EXCLUDED.level,email=EXCLUDED.email

有没有更简短的方法？只需说：使用所有排除值。

在SQLite中，我过去经常这样做：

INSERT OR REPLACE INTO tablename (id, user, password, level, email) 
                        VALUES (1, 'John', 'qwerty', 5, '[e-mail]')

- Sebastian

---

这是一条SQL语句，用于向表中插入数据或更新数据。以下是翻译后的内容： 将数据插入表中，列名为id、username、password、level和email，对应的值为1、'John'、'qwerty'、5和'[e-mail]'。如果id已存在，则执行更新操作，将username、password、level和email更新为新的值（EXCLUDED表示新插入的值，保持原有的列名）。为了方便复制/粘贴/管理列名，可以将上述语句稍作修改，如下所示：INSERT INTO tablename (id, username, password, level, email) VALUES (1, 'John', 'qwerty', 5, '[e-mail]') ON CONFLICT (id) DO UPDATE SET username = EXCLUDED.username, password = EXCLUDED.password, level = EXCLUDED.level, email = EXCLUDED.email; - foal

另一个选择是使用jsonb列，这样您就不必担心列了。 - j will

@foal 将其作为答案发布，这是一个非常有用的替代方案。 - rustyx

你不需要更新id，因为它是相同的（冲突字段）。这样会稍微简短一些。 - milan

Answer 1

Postgres没有实现与INSERT OR REPLACE等价的功能。从ON CONFLICT文档中可以看到（下划线表示强调）：

它可以是DO NOTHING，或者是一个指定在出现冲突情况时要执行的UPDATE操作确切细节的DO UPDATE子句。

尽管它不能为替换提供简写形式，但ON CONFLICT DO UPDATE更加通用，因为它允许您基于预先存在的数据设置新值。例如：

INSERT INTO users (id, level)
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE
SET level = users.level + 1;

- Kristján

---

您能否详细说明“但插入时的确切问题并没有导致更新”这句话？ - MrR

@pojo-guy - 我觉得你没有看到MrR的问题 - 你能详细说明一下“但插入时确切的问题并没有导致更新”吗？ - Randall

当您尝试在PostgreSQL中使用insert ... on update时，与合并相比，在某些特定情况下结果会有所不同。我遇到的情况非常难以捉摸和具体，但是它是可重复的。现在已经过去几个月了，所以我无法提供更多信息。 - pojo-guy

也许这不是一场冲突，而是另一个错误，比如字段类型错误？ - MrR

@Kristján用户级别是从数据库中取的吗（假设为5），还是基于级别字段中对应的值（在这种情况下为0）？如果存在冲突，新值将是6还是1？ - undefined

Answer 2

很不幸，没有更短的方法来编写那个。你必须在“do update”部分中指定每一列你想要更新的。

INSERT INTO tablename (id, username, password, level, email, update_count) 

-- if id doesn't exist, do insert
VALUES (1, 'John', 'qwerty', 5, '[e-mail]', 0) 

-- how to check for duplicates (more versatile: could use any unique index here)
ON CONFLICT (id) 
DO UPDATE 
SET 
  -- update duplicate clause
  username=EXCLUDED.username, -- references proposed insertion row
  password=EXCLUDED.password,
  level=EXCLUDED.level,
  email=EXCLUDED.email,
  update_count=tablename.update_count+1 -- reference existing row

`on conflict`会给你类似于sqlite中的`insert or replace`的功能，但它是一个更多功能的函数，更专注于`update`而不仅仅是完全替换一行。

- Nate Bosscher

---

点赞指出如何引用现有行。 - Anytoe