Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB

The error is not related to migration of data. It is due to specified space dimension device_id without specifying the number of dimensions. From the documentation of create_hypertable: `` | partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4` for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributed_hypertable.

Context: Stack Overflow #66885740 (accepted answer, 4 votes): I have a table: `` CREATE TABLE mytable ( device_id bigint NOT NULL, start TIMESTAMP WITHOUT TIME ZONE, level varchar(255) NOT NULL, amount integer ); and I want to migrate it to a hypertable like that: SELECT create_hypertable('mytable', 'start','device_id', migrate_data => true); but I get an error: ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 `` What am I doing wrong? I had similar tables, where it worked without an issue. Using PostgreSQL 11 and TimescaleDB...

Source: Stack Overflow #66885740 (accepted answer, 4 votes): I have a table: `` CREATE TABLE mytable ( device_id bigint NOT NULL, start TIMESTAMP WITHOUT TIME ZONE, level varchar(255) NOT NULL, amount integer ); and I want to migrate it to a hypertable like that: SELECT create_hypertable('mytable', 'start','device_id', migrate_data => true); but I get an error: ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 `` What am I doing wrong? I had similar tables, where it worked without an issue. Using PostgreSQL 11 and TimescaleDB...

Verified resolution: The error is not related to migration of data. It is due to specified space dimension device_id without specifying the number of dimensions. From the documentation of create_hypertable: `` | partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn. Must be > 0. Default is the number of datanodes. | For example, the create hypertable statement can be fixed to SELECT createhypertable('mytable', 'start','deviceid', 4, migratedata => true); Where the magic number 4` for number of partitions requires good reasoning. Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case: Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see createdistributed_hypertable.

Source URL: https://stackoverflow.com/questions/66885740/invalid-number-of-partitions-error-while-migrating-postgresql-table-to-timescale