Postgresql Partition by column without a primary key

Question

I'd like to be able to partition a table by worksite_uuid. But worksite_uuid needs to be nullable and I need uuid to be a primary key.

The only way I've been able create the partition is if worksite_uuid is not nullable like so:

CREATE TABLE test (
   uuid uuid DEFAULT uuid_generate_v1mc(),
   worksite_uuid text,
   notes text
)
PARTITION BY LIST(worksite_uuid)

//then add worksite_uuid and uuid as a primary key

create table test_worksite1 partition of test for values in ('1');
create table test_worksite2 partition of test for values in ('2');

Does anyone know how I can create a partition with only uuid as the primary key and make worksite_uuid nullable?

--

Example: I can't do this

CREATE TABLE test (
   uuid uuid DEFAULT uuid_generate_v1mc() PRIMARY KEY,
   worksite_uuid text,
   notes text
)
PARTITION BY LIST(worksite_uuid)

I get the following error:

Query 1 ERROR: ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "test" lacks column "worksite_uuid" which is part of the partition key.

Laurenz Albe · Accepted Answer · 2021-06-08 20:33:17Z

0

It is no problem to create a partition for the NULL values:

create table test_worksite_null partition of test for values in (NULL);

But it is impossible to have a primary key column that is nullable. You just cannot do it.

I see two ways out:

You live without a primary key. Instead, create primary keys on each individual partition. That won't guarantee global uniqueness, but almost.
You use another value instead of NULL, for example -1.

edited Jun 8, 2021 at 20:33

answered Jun 8, 2021 at 16:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bryan Over a year ago

This totally works but I can't actually get worksite_uuid to be null if it's a primary key. I've updated my question to show an example of where I am stuck

bryan Over a year ago

I don't want worksite_uuid to be a primary key. However, I can't find a way to create the table partition without it. For example, I want to run your above query to partition by null values. However, it doesn't let me because I'm forced to make worksite_uuid non-nullable. Does this make sense?

Collectives™ on Stack Overflow

Postgresql Partition by column without a primary key

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related