How to revert back to normal table from hypertable

TimescaleDB and PostgreSQL
, , , ,
1

Hello,

I created hypertable and saw a performance decline because many chunks were created (1 sec interval). This was done automatically because I did not specify INTERVAL. The documents however says that default interval of 7 days are created when nothing is specified.

Now I wan to revert back to normal table and re-specify the interval. DROP command will delete data and I do not want this.

I have combed the documentations pages but could not find the right syntax. I am new to Timescale and beginning to think this is not possible . I would be glad if my assumption is wrong here because I do not want to lose my data.

Could anyone help out here?

2

Welcome @firmphem :nerd_face:

Let’s say you want to convert your actual_hypertable into your new_hypertable. First, duplicate your data with a command to Create a table from select:

CREATE TABLE new_hypertable AS SELECT * FROM actual_hypertable;

Then you can convert it back with create_hypertable call.

select create_hypertable('new_hypertable', by_range(INTERVAL '<your new interval>'));

Now confirm that your data is present in both in the same amount:

select count(1) from new_hypertable;
select count(1) from actual_hypertable;

if they’re the same, you’re good to go and drop the old or rename it:

alter table actual_hypertable rename to old_hypertable;

With the original name free, then you can rename the new_hypertable back to actual_hypertable in the same way.

alter table new_hypertable rename to actual_hypertable;

Later, drop the old_hypertable and that’s it. :slight_smile: