Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB
Shows how to fix invalid number of partitions error while migrating PostgreSQL table to TimescaleDB. Use it when you hit this exact problem. Skip it when your error message or symptom looks different.
TL;DR
For "but I get an error:": from the documentation of create\hypertable: `` | partitioningcolumn | Name of an additional column to partition by. | | numberpartitions | Number of hash partitions to use for partitioningcolumn.
but I get an error:Steps
- The error is not related to migration of data. It is due to specified space dimension
device_idwithout specifying the number of dimensions. From the documentation ofcreate_hypertable:
2. For example, the create hypertable statement can be fixed to `SELECT create_hypertable('mytable', 'start','device_id', 4, migrate_data => 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 create_distributed_hypertable.
``` CREATE TABLE mytable ( device_id bigint NOT NULL, start TIMESTAMP WITHOUT TIME ZONE, level varchar(255) NOT NULL, amount integer ); ```
3. and I want to migrate it to a hypertable like that:
``` SELECT create_hypertable('mytable', 'start','device_id', migrate_data => true); ```
``` ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 ```
4. What am I doing wrong? I had similar tables, where it worked without an issue. Using PostgreSQL 11 and TimescaleDB...
## When to use
You are seeing this: The error is not related to migration of data. Use this skill when you run into "Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB".
## When not to use
If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.
## Versions
No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.
## Variants
Other phrasings of the same problem that show up in reports:
### `` ERROR: invalid number of partitions for dimension "device\_id" HINT: A closed (space) dimension must specify between
```text
``` ERROR: invalid number of partitions for dimension "device_id" HINT: A closed (space) dimension must specify between 1 and 32767 partitions. SQL state: 22023 ```Why this happens
The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.