Pgsql ON CONFLICT|极客教程

Pgsql ON CONFLICT

在数据库开发中，经常会遇到处理重复数据的问题。PostgreSQL引入了ON CONFLICT子句，以提供一种处理重复数据的方法。本文将详细介绍ON CONFLICT子句的使用。

1. `ON CONFLICT`子句的概述

ON CONFLICT子句是在PostgreSQL 9.5版本中引入的，它用于在插入或更新数据时处理重复冲突。在数据表中，如果有唯一约束或主键约束时，插入或更新数据时可能会发生冲突。ON CONFLICT子句提供了处理此类冲突的灵活方式。

当存在冲突时，ON CONFLICT子句允许你指定一种处理冲突的方法，例如忽略冲突、更新冲突行或插入冲突行。

2. 插入冲突数据处理示例

假设有一个名为students的数据表，包含以下列：id（整数）、name（文本）和age（整数）。我们要向students表中插入一些学生数据，并处理可能的冲突。

首先，我们创建一个students表：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE,
    age INTEGER
);

接下来，我们尝试插入一些数据，其中一些数据可能会导致冲突。我们使用INSERT INTO ... VALUES语句，并在冲突时使用ON CONFLICT子句来处理冲突。

INSERT INTO students (name, age)
VALUES ('Alice', 20),
       ('Bob', 18),
       ('Cindy', 19),
       ('Alice', 21)
ON CONFLICT (name) DO NOTHING;

在上面的示例中，我们插入了四条学生数据。由于name列具有唯一约束，插入第四条数据时会发生冲突。使用ON CONFLICT (name) DO NOTHING子句，我们告诉PostgreSQL在冲突时不采取任何操作。

运行上述插入语句后，我们可以从students表中查询数据，来验证是否成功插入了数据并处理了冲突。

SELECT * FROM students;

输出如下：

 id | name  | age
----+-------+-----
  1 | Alice |  20
  2 | Bob   |  18
  3 | Cindy |  19
(3 rows)

如你所见，第四条数据(‘Alice’, 21)并没有插入到表中，因为它与已存在的数据冲突，且我们在冲突时选择了不采取任何操作。

3. 更新冲突数据处理示例

在上述示例中，我们选择了不采取任何操作来处理冲突。而有时候，我们希望更新冲突行的某些列。我们可以使用DO UPDATE子句来实现。

下面是一个示例，我们更新age字段的值，当冲突发生时：

INSERT INTO students (name, age)
VALUES ('Alice', 20),
       ('Bob', 18),
       ('Cindy', 19),
       ('Alice', 21)
ON CONFLICT (name) DO UPDATE SET age = EXCLUDED.age;

在上述示例中，EXCLUDED指的是插入冲突行的数据。通过EXCLUDED关键字，我们可以引用冲突行的数据，并将其用于更新操作。

运行以上插入语句后，我们可以检查students表来验证冲突数据是否得到了更新。

SELECT * FROM students;

输出如下：

 id | name  | age
----+-------+-----
  1 | Alice |  21
  2 | Bob   |  18
  3 | Cindy |  19
(3 rows)

如你所见，第四条数据(‘Alice’, 21)成功更新为(‘Alice’, 21)。

4. 进一步处理冲突行

ON CONFLICT子句还可以进一步处理冲突行，例如使用WHERE子句来进行条件判断。

在下面的示例中，我们使用WHERE子句处理name列冲突，并且只更新满足条件的冲突行，即age > EXCLUDED.age。

INSERT INTO students (name, age)
VALUES ('Alice', 20),
       ('Bob', 18),
       ('Cindy', 22),
       ('Alice', 19)
ON CONFLICT (name)
DO UPDATE SET age =
    CASE
        WHEN students.age > EXCLUDED.age THEN EXCLUDED.age
        ELSE students.age
    END;

在上述示例中，我们使用CASE语句进行条件判断，并在冲突时根据条件更新age字段的值。

运行以上插入语句后，我们可以从students表中查询数据来验证冲突行是否得到了处理。

SELECT * FROM students;

输出如下：

 id | name  | age
----+-------+-----
  1 | Alice |  19
  2 | Bob   |  18
  3 | Cindy |  22
(3 rows)

如你所见，第四条数据(‘Alice’, 19)成功更新为(‘Alice’, 19)，因为冲突行的age值小于已存在的行。

5. 使用唯一约束处理冲突

除了使用列约束处理冲突外，我们还可以使用唯一约束来处理冲突。唯一约束是在表级别创建，而不是在列级别创建。

下面是一个示例，演示如何使用唯一约束来处理冲突。

首先，我们创建一个名为students的数据表，其中name列具有唯一约束。

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INTEGER,
    CONSTRAINT unique_name UNIQUE (name)
);

接下来，我们使用INSERT INTO ... VALUES语句插入一些数据，并处理冲突。

INSERT INTO students (name, age)
VALUES ('Alice', 20),
       ('Bob', 18),
       ('Cindy', 22),
       ('Alice', 19)
ON CONFLICT ON CONSTRAINT unique_name DO UPDATE SET age = EXCLUDED.age;

在上述示例中，我们使用ON CONFLICT ON CONSTRAINT unique_name语句来指定唯一约束名称，并在冲突时更新age字段的值。

运行以上插入语句后，我们可以从students表中查询数据，来验证冲突行是否得到了处理。

SELECT * FROM students;

输出如下：

 id | name  | age
----+-------+-----
  1 | Alice |  19
  2 | Bob   |  18
  3 | Cindy |  22
(3 rows)

如你所见，第四条数据(‘Alice’, 19)成功更新为(‘Alice’, 19)，因为冲突行的age值小于已存在的行。

6. `DO NOTHING`和`DO UPDATE`的区别

在使用ON CONFLICT子句时，可以选择使用DO NOTHING或DO UPDATE来处理冲突。它们之间的区别在于，DO NOTHING会忽略冲突行，不采取任何操作，而DO UPDATE会更新冲突行的某些列。

下面是一个简单的比较示例。

假设我们有一个名为users的表，其中包含id（唯一约束）和name列。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

现在我们尝试使用DO NOTHING和DO UPDATE来处理冲突。

使用DO NOTHING：

INSERT INTO users (id, name)
VALUES (1, 'Alice'),
       (2, 'Bob'),
       (1, 'Cindy')
ON CONFLICT (id) DO NOTHING;

使用DO UPDATE：

INSERT INTO users (id, name)
VALUES (1, 'Alice'),
       (2, 'Bob'),
       (1, 'Cindy')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

对于上述两个示例，我们可以从users表中查询数据，来验证冲突行是否得到了处理。

SELECT * FROM users;

使用DO NOTHING的输出如下：

 id | name
----+------
  1 | Alice
  2 | Bob
(2 rows)

使用DO UPDATE的输出如下：

 id | name
----+-------
  1 | Cindy
  2 | Bob
(2 rows)

如你所见，使用DO NOTHING时，冲突行(‘Cindy’)被忽略，而使用DO UPDATE时，冲突行的name字段被更新为(‘Cindy’)。

7. 在多个唯一约束的情况下处理冲突

在一张表中可以有多个唯一约束，如果冲突发生时，我们可以使用ON CONFLICT子句处理。

下面是一个示例，演示如何在多个唯一约束的情况下处理冲突。

假设我们有一个名为books的表，其中包含id、title和isbn列。title列和isbn列都设置了唯一约束。

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT UNIQUE,
    isbn TEXT UNIQUE
);

现在，我们尝试插入一些数据，其中一些数据可能会导致冲突。

INSERT INTO books (title, isbn)
VALUES ('Book1', '123456'),
       ('Book2', '789012'),
       ('Book1', '345678'),
       ('Book3', '123456')
ON CONFLICT (title) DO NOTHING
ON CONFLICT (isbn) DO UPDATE SET title = EXCLUDED.title;

在上述示例中，我们使用两个ON CONFLICT子句来处理多个唯一约束的冲突。第一个子句ON CONFLICT (title) DO NOTHING告诉PostgreSQL在冲突时不采取任何操作。第二个子句ON CONFLICT (isbn) DO UPDATE SET title = EXCLUDED.title告诉PostgreSQL在冲突时更新title字段的值为冲突行的title值。

运行以上插入语句后，我们可以从books表中查询数据，来验证冲突行是否得到了处理。

SELECT * FROM books;

输出如下：

 id | title |  isbn
----+-------+--------
  1 | Book1 | 123456
  2 | Book2 | 789012

如你所见，冲突行(‘Book1’, ‘345678’)被忽略，而冲突行(‘Book3’, ‘123456’)的title字段成功更新为(‘Book1’)。

8. 总结

通过本文，我们了解了ON CONFLICT子句的使用方法。它可以在插入或更新数据时处理重复冲突，提供了灵活的处理方式。我们可以通过DO NOTHING、DO UPDATE和WHERE子句来处理冲突行，并且可以在多个唯一约束的情况下使用ON CONFLICT子句。

使用ON CONFLICT子句可以提高数据库的性能，并简化数据冲突的处理逻辑。它是PostgreSQL提供的非常有用的功能之一，可以在实际的数据库开发中大显身手。

Pgsql ON CONFLICT