插入或更新数据 (Upsert data)

插入或更新 (Upsert) 是一种执行以下操作的操作：

• 如果不存在匹配的行，则插入新行
• 如果已存在匹配的行，则更新现有行或不执行任何操作

插入或更新 (Upsert) 仅在您具有唯一索引或约束时才有效。匹配的行是指在索引或约束涵盖的列中具有相同值的行。

注意

在 PostgreSQL 中，主键是具有 NOT NULL 约束的唯一索引。如果您有主键，则会自动拥有唯一索引。

本节中的示例使用一个 conditions 表，该表在 (time, location) 列上具有唯一约束。要创建唯一约束，请在定义表时使用 UNIQUE (<COLUMNS>)

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL,
  UNIQUE (time, location)
);

复制

您也可以在创建表后创建唯一约束。使用语法 ALTER TABLE ... ADD CONSTRAINT ... UNIQUE。在此示例中，约束命名为 conditions_time_location

ALTER TABLE conditions
  ADD CONSTRAINT conditions_time_location
    UNIQUE (time, location);

复制

当您向表中添加唯一约束时，您无法插入违反约束的数据。换句话说，如果您尝试插入的数据在约束涵盖的列中与另一行具有相同的值，则会收到错误。

注意

唯一约束必须包含所有分区列。这意味着超表上的唯一约束必须包含时间列。如果您向超表添加了其他分区列，则约束也必须包含这些列。有关更多信息，请参阅关于超表和唯一索引的部分。

您可以告诉数据库，如果新数据不违反约束，则插入新数据；如果违反约束，则更新现有行。使用语法 INSERT INTO ... VALUES ... ON CONFLICT ... DO UPDATE。

例如，要更新 temperature 和 humidity 值（如果已存在具有指定 time 和 location 的行），请运行

INSERT INTO conditions
  VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.2, 50.1)
  ON CONFLICT (time, location) DO UPDATE
    SET temperature = excluded.temperature,
        humidity = excluded.humidity;

复制

您还可以告诉数据库，如果违反约束，则不执行任何操作。新数据不会插入，旧行也不会更新。这在将多行作为一个批次写入时非常有用，以防止整个事务失败。数据库引擎会跳过该行并继续。

要插入或不执行任何操作，请使用语法 INSERT INTO ... VALUES ... ON CONFLICT DO NOTHING

INSERT INTO conditions
  VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.1, 50.0)
  ON CONFLICT DO NOTHING;

复制