Insert Command in TimescaleDB
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