1

[Question posted by a user on YugabyteDB Community Slack]

Is it possible to create a partitioned table in a colocated database? When the database is created with colocated=true and trying to add a partitioned table like this:

create table test(id bigserial not null, PRIMARY KEY(id HASH)) PARTITION BY RANGE WITH (colocated = false);

I’m getting an error

Query 1 ERROR: ERROR:  syntax error at or near “WITH” LINE 3:   PRIMARY KEY(id HASH)) PARTITION BY RANGE WITH (colocated=t…

Is it possible to do this or should I think about some other approach? I’m trying to do geo-partitioning and at the same time have some of the tables colocated.

dh YB
  • 965
  • 3
  • 10

1 Answers1

1

The syntax is wrong: you need to specify which columns to PARTITION BY RANGE. For example, PARTITION BY RANGE (id) (but then why is it a hash primary key?)

You can't have a hash partitioned table for colocation. In your case, since the table is partitioned, it should work (as long as you fix the syntax error), but all partitions under it can't be colocated.

Taking into account the above, you can have something like:

create table new (id bigserial not null, PRIMARY KEY (id ASC)) partition by range(id);
create table new_1 partition of new for values from (5) to (10) with (colocated = false);
create table new_2 partition of new for values from (20) to (30) with (colocated = true);

You can't shard by hash if you want to set colocated=true. It works fine with colocated=false:

create table new (id bigserial not null, value text) partition by range(id);
create table new_1 partition of new (primary key(id hash)) for values from (0) to (5) with (colocated = false);
create table new_2 partition of new (primary key(id hash)) for values from (5) to (10) with (colocated = false);
dh YB
  • 965
  • 3
  • 10