From: | Holger Jakobs <holger(at)jakobs(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Partitioning on the date part of a timestamp & PK issues |
Date: | 2021-07-15 20:17:56 |
Message-ID: | 4C13EFB8-F25D-4361-817B-DA997FB15912@jakobs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Am 15. Juli 2021 22:10:01 MESZ schrieb Wells Oliver <wells(dot)oliver(at)gmail(dot)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.
>
>--
>Wells Oliver
>wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
--
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 -
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-07-15 20:30:10 | Re: Partitioning on the date part of a timestamp & PK issues |
Previous Message | Wells Oliver | 2021-07-15 20:10:01 | Partitioning on the date part of a timestamp & PK issues |