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 Laurenz Albe · Accepted Answer · 2021-06-08T16:07:56

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.

Postgresql Partition by column without a primary key

1 Answers