Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB

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

1. 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:

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.

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