PostgreSQL upsert|极客教程

PostgreSQL upsert

在数据库操作中，经常会遇到需要插入新数据或更新已有数据的情况。在 PostgreSQL 中，可以使用 INSERT... ON CONFLICT 语句来实现这个功能，也被称为 upsert（合并插入和更新）。

什么是 upsert？

Upsert 操作是指在插入数据时，如果数据库中已存在相同的记录，则更新已有记录；如果数据库中不存在相同的记录，则插入新记录。这种操作能够避免插入数据时出现重复数据，同时也能够保证数据的完整性和准确性。

在 PostgreSQL 中，upsert 操作可以通过 INSERT... ON CONFLICT 语句来实现。

如何使用 `INSERT... ON CONFLICT` 实现 upsert？

下面是一个示例来演示如何使用 INSERT... ON CONFLICT 来实现 upsert 操作：

-- 创建一个测试表
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    age INT
);

-- 插入一条新记录
INSERT INTO test_table (name, age) VALUES ('Alice', 25)
ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age;

-- 再次插入同样的数据
INSERT INTO test_table (name, age) VALUES ('Alice', 27)
ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age;

-- 查询已有数据
SELECT * FROM test_table;

在上面的示例中，我们首先创建了一个名为 test_table 的表，表结构包括 id、name 和 age 字段，其中 name 字段被设置为唯一约束。然后我们执行插入操作，插入数据 ‘Alice’、25。如果数据库中已存在 ‘Alice’ 的记录，则更新 age 字段的值为插入数据的 age。

执行结果

在执行上述示例代码后，我们可以得到以下结果：

 id | name  | age
----+-------+-----
  1 | Alice |  27

可以看到，尽管我们多次插入了相同的数据 ‘Alice’，但是数据库中只保留了一条记录，并更新了年龄值。

`ON CONFLICT` 的其他用法

除了上面示例中介绍的用法，ON CONFLICT 还有其他几种用法，下面列举一些常用的情况：

如果需要插入数据但不希望更新已有数据，可以使用 DO NOTHING：

INSERT INTO test_table (name, age) VALUES ('Bob', 30) ON CONFLICT DO NOTHING;

如果希望更新所有字段而不只是部分字段，可以使用 DO UPDATE SET：

INSERT INTO test_table (name, age) VALUES ('Alice', 29) ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

如果需要对不同的冲突情况进行不同的处理，可以使用 ON CONFLICT ... DO NOTHING 结合 CASE 语句：

INSERT INTO test_table (name, age) VALUES ('Alice', 29)
ON CONFLICT (name) 
DO UPDATE SET age = CASE
    WHEN EXCLUDED.age > test_table.age THEN EXCLUDED.age
    ELSE test_table.age
END;