插入或更新数据

插入或更新操作同时执行以下功能：

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

插入或更新操作仅在您拥有唯一索引或约束时才有效。匹配行是指其索引或约束覆盖的列具有相同值的行。

注意

在 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。

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

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;

复制