For this example we'll assume that you have a table named
old_table that you
want to migrate to a table named
new_table. The steps are:
There are two ways to go about this step: one more convenient, the other faster.
This method recreates
old_table indexes on
new_table when it is created so that
when we convert it to a hypertable in the next step, we don't have to make them
ourselves. It avoids a step, but slows down the data transfer due to the need to
update the indexes for each migrated row.
CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
This method does not generate the indexes while making the table. This makes the data transfer faster than the convenient method, but requires us to add the indexes as a final step.
CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
We use the TimescaleDB function
new_table to a hypertable, then simply
INSERT data from the old table:
-- Assuming 'time' is the time column for the dataset SELECT create_hypertable('new_table', 'time'); -- Insert everything from old_table INSERT INTO new_table SELECT * FROM old_table;
If you used the convenient method, whatever indexes were on
old_table are now
new_table making this step optional. For the faster
CREATE TABLE method
or for adding any indexes not on
old_table, you need to add indexes to
CREATE INDEX on new_table (column_name, <options>)
Tada! You did it!
For more info on the best strategies for indexing, check out our schema management section.
Now check out some common hypertable commands for exploring your data.