Insert Command in TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorials we will show you how to use the insert command in TimescaleDB with an easy to follow demonstration. First we’ll list some recommended prerequisites and then jump into how to insert a single record into a hypertable. We’ll also do a demo of how to insert multiple records into a hypertable. We hope that you find this information helpful and join us for more tutorials.

Prerequisite

Ensure to install and configure the following for your OS:

Basic PostgreSQL is required, to be able to follow along with the tutorial.

Insert Command In TimescaleDB

TimescaleDB fully supports the Structured Query Language also known as SQL, so the commands for performing INSERT statement will be familiar to us. We can insert data into a hypertable via standard INSERT statement.

Inserting Single Record into Hypertable Example

In this section, we will be looking at how to insert a single record into a hypertable via the INSERT command. We will be using the following statement.

1
2
INSERT INTO TABLE_NAME(<column_list>)
VALUES (value1, value2, ...);
  • First, we call the INSERT INTO clause followed by the ‘table_name’.
  • Second, we specify the column names we want to insert the new records.
  • Third, we specify the values that we will be storing in the hypertable.

Consider the sample table structure that we will be using for our demo purposes.

1
2
3
4
5
CREATE TABLE tremor (
 TIME       TIMESTAMPTZ       NOT NULL,
 location   TEXT              NOT NULL,
 magnitude  DOUBLE PRECISION  NULL
);

Then we transform the table ‘tremor’ into a hypertable via create_hypertable command.

1
SELECT create_hypertable('tremor', 'time');

Now we can perform the INSERT operation.

1
2
INSERT INTO tremor(TIME, location, magnitude)
VALUES (NOW(), 'olongapo', 4.0);

We can query the tremor table to check if we successfully inserted the record.

Take a look at the output:

1
2
3
4
5
tremorsdb=# select * from tremor;
             time              | location | magnitude
-------------------------------+----------+-----------
 2020-03-19 21:54:48.236543+08 | olongapo |         4
(1 row)

Inserting Multiple Record into Hypertable Example

In the previous section, we showed you how to insert a single record in a hypertable, now in this section, we will show you how to insert multiple records.

We will be using the same table in the previous section which is the tremor table.

The basic form of inserting multiple records is as follows.

1
2
3
4
INSERT INTO TABLE_NAME(<column_list>)
VALUES
 (value1, value1.1, ...),
 (value2, value2.1, ...);
  • Again, we call the clause INSERT INTO followed by the name of the table.
  • Then we specify the columns or the table field.
  • Then we specify the values we want to insert against the fields, notice that we separate every batch via parenthesis ‘( )’.

Now we can perform how to insert multiple records in the hypertable.

1
2
3
4
5
INSERT INTO tremor(TIME, location, magnitude)
VALUES
(NOW(), 'makati', 4.0),
(NOW(), 'manila', 5.0),
(NOW(), 'corrigedor', 5.0);

We can query again our table to verify that the new three (3) records were successfully inserted into the table.

The resulting output is below:

1
2
3
4
5
6
7
8
tremorsdb=# select * from tremor;
             time              |  location  | magnitude
-------------------------------+------------+-----------
 2020-03-19 21:54:48.236543+08 | olongapo   |         4
 2020-03-19 22:07:50.037065+08 | makati     |         4
 2020-03-19 22:07:50.037065+08 | manila     |         5
 2020-03-19 22:07:50.037065+08 | corrigedor |         5
(4 rows)

NOTE: This way of inserting records is more efficient than doing it via row-by-row basis.

Conclusion

We hope you found this tutorial on how to use the insert command in TimescaleDB useful. Inserting records is something you should have down cold so we hope this article helped you along that path. We have more articles on various topics concerning TimescaleDB so please check out our knowledge base for more information.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.