UPSERT 功能

TimescaleDB supports UPSERTs in the same manner as PostgreSQL via the optional ​ON CONFLICT​ clause (​PostgreSQL docs​). If such a clause is provided, rather than cause an error, an inserted row that conflicts with another can either (a) do nothing or (b) result in a subsequent update of that existing row.

TimescaleDB和PostgreSQL一样可以通过ON CONFLICT​语句支持UPSERTs。使用该语句时，如果不报错，新插入的与数据库中冲突的数据行或者不做操作，或者更新现有的数据行。

In order to create a conflict, an insert must be performed on identical value(s) in column(s) covered by a unique index. Such an index is created automatically when marking column(s) as PRIMARY KEY or with a UNIQUE constraint.

为了创建conflict，必须在唯一键索引所在的列中的相同值上执行插入操作。当将列标记为主键或具有唯一约束时，会自动创建这样一个索引。

Following the examples given above, an INSERT with an identical timestamp and location as an existing row will succeed and create an additional row in the database.

按照上面给出的示例，具有相同时间戳和位置的INSERT作为现有行将继承并在数据库中创建一个额外的行。

If, however, the ​conditions​ table had been created with a UNIQUE constraint defined on one or more of the columns (either at table creation time or via an ALTER command):

但是，如果表的条件是用一个或多个列上定义的唯一约束创建的（在表创建时，或通过ALERT命令）：

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

then the second attempt to insert to this same time will normally return an error.

接下来再次尝试插入相同时间时一般会返回一个错误。

Now, however, the INSERT command can specify that nothing be done on a conflict. This is particularly important when writing many rows as one batch, as otherwise the entire transaction will fail (as opposed to just skipping the row that conflicts).

但是，现在，INSERT命令可以指定在冲突中不做任何处理。当将多行数据作为一批数据写入时，这一点尤为重要，否则整个事务将失败（与跳过冲突的行相反）。

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

Alternatively, one can specify how to update the existing data:

或者，可以指定更新现有的数据的方法：

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;

WARNING:Unique constraints must include all partitioning keys as their prefix. This above example works if either time, or time and location, are used to partition the hypertable (i.e., are the arguments to ​create_hypertable​). If the schema had an additional column like ​device​ which was alternatively used for space partitioning, then the constraint would have to be ​UNIQUE(time, device)​ or ​UNIQUE(time, device, location)​.

唯一约束必须包含所有的分区键，并将分区键作为前缀。如果使用时间，或者时间和位置分割hypertable时，上面的例子将会正常进行。如果数据库对象集合有一个额外的列，比如说用来分割空间的设备列，那么

WARNING:TimescaleDB does not yet support ​ON CONFLICT ON CONSTRAINT​ functionality, so conflicts must currently occur over a unique index. This limitation will be removed in a future version. For now, the suggested approach is either to explicitly define a unique index or specify that one column is UNIQUE.