Insert data

Insert data into a hypertable with a standard INSERT SQL command.

To insert a single row into a hypertable, use the syntax INSERT INTO ... VALUES. For example, to insert data into a hypertable named conditions:

INSERT INTO conditions(time, location, temperature, humidity)
  VALUES (NOW(), 'office', 70.0, 50.0);

Copy

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

Use the same syntax, separating rows with a comma:

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

Copy

Note

You can insert multiple rows belonging to different chunks within the same INSERT statement. Behind the scenes, TimescaleDB batches the rows by chunk, and writes to each chunk in a single transaction.

In the same INSERT command, you can return some or all of the inserted data by adding a RETURNING clause. For example, to return all the inserted data, run:

INSERT INTO conditions
  VALUES (NOW(), 'office', 70.1, 50.1)
  RETURNING *;

Copy

This returns:

time                          | location | temperature | humidity
------------------------------+----------+-------------+----------
2017-07-28 11:42:42.846621+00 | office   |        70.1 |     50.1
(1 row)

Copy

This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

To improve performance, you can compress data during INSERT so that it is injected directly into chunks in the columnstore rather than waiting for the policy.

To enable direct compress on INSERT, enable the following GUC parameters:

SET timescaledb.enable_compressed_insert = true;
SET timescaledb.enable_compressed_insert_sort_batches = true;
SET timescaledb.enable_compressed_insert_client_sorted = true;

Copy

When you set enable_compressed_insert_client_sorted to true, you must ensure that data in the input stream is sorted.