Date: Thu, 15 Jul 2021 22:17:56 +0200
User-Agent: K-9 Mail for Android
In-Reply-To: 
 <CAOC+FBVxcK2fdNXu30LvWkiLuQ3+qSxke5nPajzjaVayHOeRaw@mail.gmail.com>
References: 
 <CAOC+FBVxcK2fdNXu30LvWkiLuQ3+qSxke5nPajzjaVayHOeRaw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----F31K14VBZY27HDMKWUTVRFPFSPQMX8"
Content-Transfer-Encoding: 7bit
Subject: Re: Partitioning on the date part of a timestamp & PK issues
To: pgsql-admin@lists.postgresql.org
From: Holger Jakobs <holger@jakobs.com>
Message-ID: <4C13EFB8-F25D-4361-817B-DA997FB15912@jakobs.com>
Archived-At: 
 <https://www.postgresql.org/message-id/4C13EFB8-F25D-4361-817B-DA997FB15912%40jakobs.com>
Precedence: bulk

------F31K14VBZY27HDMKWUTVRFPFSPQMX8
Content-Type: text/plain;
 charset=utf-8
Content-Transfer-Encoding: quoted-printable


Am 15=2E Juli 2021 22:10:01 MESZ schrieb Wells Oliver <wells=2Eoliver@gmai=
l=2Ecom>:
>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=2E
>
>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=2E
>
>The PK is the PK, so I'm not sure what options I have there=2E
>
>Thanks=2E
>
>--=20
>Wells Oliver
>wells=2Eoliver@gmail=2Ecom <wellsoliver@gmail=2Ecom>

--=20
Maybe you could sister separate date and time and use date as partition ke=
y=2E The timestamp column could be generated in a view as datecol + timecol=
=2E

If new data is entered, it could be separated into the two columns via tri=
gger=2E

Haven't tried it, bit seems feasible=2E

Regards,
Holger

--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

------F31K14VBZY27HDMKWUTVRFPFSPQMX8
Content-Type: text/html;
 charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><br><br><div class=3D"gmail_quote">Am 15=2E Juli 2=
021 22:10:01 MESZ schrieb Wells Oliver &lt;wells=2Eoliver@gmail=2Ecom&gt;:<=
blockquote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0=2E8ex; bord=
er-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div dir=3D"ltr">I have a table like so:<div><br></div><div>CREATE TABLE t=
 (<br>&nbsp; &nbsp; guid uuid not null,<br>&nbsp; &nbsp; seq smallint not n=
ull,<br>&nbsp; &nbsp; tid smallint not null,<br>&nbsp; &nbsp; ts timestamp =
without time zone not null,<br>&nbsp; &nbsp; x real,<br>&nbsp; &nbsp; y rea=
l,<br>&nbsp; &nbsp; z real,<br>&nbsp; &nbsp; primary key (guid, tid, seq)<b=
r>) partition by range (extract(date from ts));</div><div><br></div><div>Wh=
ich results in the error:</div><div><br></div><div>ERROR: &nbsp;unsupported=
 PRIMARY KEY constraint with partition key definition<br>DETAIL: &nbsp;PRIM=
ARY KEY constraints cannot be used when partition keys include expressions=
=2E<br><div><br></div><div>Is there a suitable way to get around&nbsp;this?=
 I can make an additional date col in the table to use for the partition ra=
nge, but given the amount of data we will have, I am trying to be cognizant=
 of storage concerns=2E</div><div><br></div><div>The PK is the PK, so I'm n=
ot sure what options I have there=2E</div><div><br></div><div>Thanks=2E</di=
v><div><br></div>-- <br><div dir=3D"ltr" class=3D"gmail_signature" data-sma=
rtmail=3D"gmail_signature"><div dir=3D"ltr"><div>Wells Oliver<br><a href=3D=
"mailto:wellsoliver@gmail=2Ecom" target=3D"_blank">wells=2Eoliver@gmail=2Ec=
om</a></div></div></div></div></div>
</blockquote></div><br>-- <br>Maybe you could sister separate date and tim=
e and use date as partition key=2E The timestamp column could be generated =
in a view as datecol + timecol=2E<br><br>If new data is entered, it could b=
e separated into the two columns via trigger=2E<br><br>Haven't tried it, bi=
t seems feasible=2E<br><br>Regards,<br>Holger<br><br>--<br>Holger Jakobs, B=
ergisch Gladbach<br>+49 178 9759012<br>- sent from mobile, therefore short =
-<br></body></html>
------F31K14VBZY27HDMKWUTVRFPFSPQMX8--