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;
create_hypertablemay fail if invalid UNIQUE or PRIMARY KEY indexes existed on the old table (see this note). In this case, you would have to reconfigure your indexes and/or schema.
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.
Found an issue on this page?Report an issue!