pgsql upsert|极客教程

pgsql upsert

在 PostgreSQL 中，upsert 是一种常见的操作，它结合了插入（insert）和更新（update）两种操作，能够在数据存在时更新数据，在数据不存在时插入新数据。这在实际的开发中十分常见，因此掌握 upsert 的用法是非常重要的。

在 PostgreSQL 中，upsert 操作实际上是在插入操作的基础上进行更新。在实际中，我们可以通过使用 INSERT INTO … ON CONFLICT DO UPDATE 语句来实现 upsert 操作。

基本语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target) DO UPDATE
SET column1 = value1, column2 = value2, ...;

其中，table_name 是表的名称，column1, column2, ... 是表的列名，value1, value2, ... 是需要插入的值。conflict_target 是冲突检测的关键字，可以是具体的列名也可以是约束。

在 DO UPDATE 部分，我们需要设置更新的列和对应的值，以及更新条件。在这里我们可以使用 EXCLUDED 关键字来引用插入的新值。

下面我们来看一个示例：

CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    age INT
);

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

在这个示例中，我们创建了一个 test 表，包含 id、name 和 age 三个列。接着我们向表中插入了一条数据，如果 name 列发生冲突（即已经存在），则对 age 列进行更新。

冲突检测

在上面的示例中，我们通过 ON CONFLICT (name) 来指定冲突检测的列为 name。实际上，我们还可以使用其他的约束来进行冲突检测，比如主键、唯一约束等。

另外，我们还可以通过 ON CONFLICT DO NOTHING 来忽略冲突不进行操作。例如：

INSERT INTO test (name, age) VALUES ('Alice', 30)
ON CONFLICT (name) DO NOTHING;

返回处理结果

在进行 upsert 操作时，我们可以通过 RETURNING 子句获取操作的结果。这个结果可以是插入时的值，也可以是更新时的值，这对于我们进行后续操作非常有帮助。

INSERT INTO test (name, age) VALUES ('Alice', 30)
ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age
RETURNING *;

多列冲突检测

有时候，我们需要使用多列进行冲突检测，这时候可以在 ON CONFLICT 子句中指定多个列名。例如：

CREATE TABLE test2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    score INT
);

INSERT INTO test2 (name, score) VALUES ('Alice', 80)
ON CONFLICT (name, score) DO NOTHING;