PostgreSQL在冲突更新中插入（向上更新）使用所有排除的值 [英] PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

问题描述

当您向上插入一行（PostgreSQL> = 9.5），并且希望可能的INSERT与可能的UPDATE完全相同时，可以这样写：

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this:

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

有更短的方法吗？只是说：使用所有EXCLUDE值。

Is there a shorter way? To just say: use all the EXCLUDE values.

在SQLite中，我曾经做过：

In SQLite I used to do :

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

推荐答案

Postgres尚未实现与插入或替换。从 ON CONFLICT 文档（重点是我的）：

Postgres hasn't implemented an equivalent to INSERT OR REPLACE. From the ON CONFLICT docs (emphasis mine):

它可以是不做或指定的DO UPDATE子句发生冲突时要执行的UPDATE操作的确切详细信息。

尽管它不会给您速记替换时， ON CONFLICT DO UPDATE 更适用，因为它可以让您根据现有数据设置新值。例如：

Though it doesn't give you shorthand for replacement, ON CONFLICT DO UPDATE applies more generally, since it lets you set new values based on preexisting data. For example:

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

这篇关于PostgreSQL在冲突更新中插入（向上更新）使用所有排除的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！