Partitioning on the date part of a timestamp & PK issues

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Partitioning on the date part of a timestamp & PK issues
Date: 2021-07-15 20:10:01
Message-ID: CAOC+FBVxcK2fdNXu30LvWkiLuQ3+qSxke5nPajzjaVayHOeRaw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have a table like so:

CREATE TABLE t (
guid uuid not null,
seq smallint not null,
tid smallint not null,
ts timestamp without time zone not null,
x real,
y real,
z real,
primary key (guid, tid, seq)
) partition by range (extract(date from ts));

Which results in the error:

ERROR: unsupported PRIMARY KEY constraint with partition key definition
DETAIL: PRIMARY KEY constraints cannot be used when partition keys include
expressions.

Is there a suitable way to get around this? I can make an additional date
col in the table to use for the partition range, but given the amount of
data we will have, I am trying to be cognizant of storage concerns.

The PK is the PK, so I'm not sure what options I have there.

Thanks.

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Holger Jakobs 2021-07-15 20:17:56 Re: Partitioning on the date part of a timestamp & PK issues
Previous Message Matti Linnanvuori 2021-07-15 11:23:53 Re: Upgrade postgresql