UPSERT functionality

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.

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

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.

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):

  1. CREATE TABLE conditions (
  2.     time        TIMESTAMPTZ       NOT NULL,
  3.     location    TEXT              NOT NULL,
  4.     temperature DOUBLE PRECISION  NULL,
  5.     humidity    DOUBLE PRECISION  NULL,
  6.     UNIQUE (time, location)
  7. );

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

The above UNIQUE statement during table creation internally is similar to:

  1. CREATE UNIQUE INDEX on conditions (time, location);

Both of these result in a unique index for the table:

  1. # \d+ conditions;
  2.                               Table "public.conditions"
  3.    Column    |           Type           | Modifiers | Storage  | Stats target | Description
  4. -------------+--------------------------+-----------+----------+--------------+-------------
  5.  time        | timestamp with time zone | not null  | plain    |              |
  6.  location    | text                     | not null  | extended |              |
  7.  temperature | double precision         |           | plain    |              |
  8.  humidity    | double precision         |           | plain    |              |
  9. Indexes:
  10.     "conditions_time_location_idx" UNIQUE, btree ("time", location)

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).

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

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

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

tip

Unique constraints must include all partitioning keys. For example, if the table just uses time partitioning, the system requires time as part of the constraint: UNIQUE(time), UNIQUE(time, location), UNIQUE(location, time), etc. On the other hand, UNIQUE(location) is _not_ a valid constraint.

If the schema were to have an additional column like device that is used as an additional partition dimension, then the constraint would have to be UNIQUE(time, device) or UNIQUE(time, device, location). In such scenarios then, UNIQUE(time, location) would _no longer_ be a valid constraint.

warning

TimescaleDB does not yet support using ON CONFLICT ON CONSTRAINT with a named key (e.g., conditions_time_location_idx), but much of this functionality can be captured by specifying the same columns as above with a unique index/constraint. This limitation will be removed in a future version.