Am 15. Juli 2021 22:10:01 MESZ schrieb Wells Oliver <wells.oliver@gmail.com>:
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.
--
--
Maybe you could sister separate date and time and use date as partition key. The timestamp column could be generated in a view as datecol + timecol.
If new data is entered, it could be separated into the two columns via trigger.
Haven't tried it, bit seems feasible.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -