It is not possible to turn unlogged tables into hypertables

TimescaleDB and PostgreSQL
1

On PostgreSQL v15.4 using TimescaleDB v2.11.2 I have got a request from a coworker to change data model for one large hypertable. I need to create new table and then insert into this new table from old table.

On ordinary PostgreSQL table I can create “unlogged” table for the duration of initial insert, then alter table back to “logged”. This unlogged table is also supported for PostgreSQL partition tables like:

CREATE UNLOGGED TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
CREATE UNLOGGED TABLE cust_arr_small PARTITION OF customers FOR VALUES FROM (MINVALUE) TO (25);
CREATE UNLOGGED TABLE cust_arr_medium PARTITION OF customers FOR VALUES FROM (25) TO (75);
CREATE UNLOGGED TABLE cust_arr_large PARTITION OF customers FOR VALUES FROM (75) TO (MAXVALUE);

But to do the same for TimescaleDB table:

CREATE UNLOGGED TABLE TS_CUSTOMER (time TIMESTAMP, id INT);
SELECT create_hypertable('ts_customer','time');

And error is returned:

WARNING: column type “timestamp without time zone” used for “time” does not follow best practices
HINT: Use datatype TIMESTAMPTZ instead.
ERROR: table “ts_customer” has to be logged
DETAIL: It is not possible to turn temporary or unlogged tables into hypertables.

Is there any other recommendation for fast insert/load/copy data from first hypertable to second hypertable?

2

Hi @ts101 , I think you’ll need to have a regular table in the middle.

Try to CREATE TABLE ts_customer as SELECT * FROM unlogged_ts_customer and then
you can migrate data directly.

SELECT create_hypertable('ts_customer','time', migrate_data => true);