Writing data

Data can be inserted into a hypertable using the standard SQL command (PostgreSQL docs).

You can also insert multiple rows into a hypertable using a single INSERT call, even thousands at a time. This is typically much more efficient than inserting data row-by-row, and is recommended when possible.

INSERT INTO conditions
  VALUES
    (NOW(), 'office', 70.0, 50.0),
    (NOW(), 'basement', 66.5, 60.0),
    (NOW(), 'garage', 77.0, 65.2);

You can also specify that INSERT returns some or all of the inserted data via the RETURNING statement:

INSERT INTO conditions
  VALUES (NOW(), 'office', 70.1, 50.1) RETURNING *;
             time              | location | temperature | humidity
 2017-07-28 11:42:42.846621+00 | office   |        70.1 |     50.1
(1 row)

Updates in TimescaleDB work as expected in standard SQL ().

An update command can touch many rows at once, i.e., the following will modify all rows found in a 10-minute block of data.

UPDATE conditions SET temperature = temperature + 0.1
  WHERE time >= '2017-07-28 11:40' AND time < '2017-07-28 11:50';

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

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.

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

# \d+ conditions;
                              Table "public.conditions"
-------------+--------------------------+-----------+----------+--------------+-------------
 time        | timestamp with time zone | not null  | plain    |              |
 location    | text                     | not null  | extended |              |
 temperature | double precision         |           | plain    |              |
 humidity    | double precision         |           | plain    |              |
Indexes:
    "conditions_time_location_idx" UNIQUE, btree ("time", location)

Now, however, the 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 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:

Data can be deleted from a hypertable using the standard DELETE SQL command (), which will propagate down to the appropriate chunks that comprise the hypertable.

DELETE FROM conditions WHERE temperature < 35 OR humidity < 60;
DELETE FROM conditions WHERE time < NOW() - INTERVAL '1 month';