Re: Partitioning on the date part of a timestamp & PK issues

Thanks all, much clearer now.

On Thu, Jul 15, 2021 at 3:52 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> Use RANGE partitioning.
> https://www.postgresql.org/docs/12/ddl-partitioning.html
>
> 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, ts)
> ) partition by range (ts);
>
> CREATE TABLE t_y2021m07 PARTITION OF measurement
> FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
>
> CREATE TABLE measurement_y2021m08 PARTITION OF measurement
> FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
>
> On 7/15/21 5:09 PM, Wells Oliver wrote:
>
> Correct me if I'm wrong, but wouldn't partitioning by just the ts column
> create a ton of partitions per the granular nature of timestamps? Or do I
> wildly misunderstand this? Another Very Large Table we have partitioned by
> a date field cleanly creates monthly partitions.
>
> On Thu, Jul 15, 2021 at 3:06 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On 7/15/21 3:10 PM, Wells Oliver wrote:
>> > 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.
>>
>> Why do you feel the need to "extract(date from ts)" instead of just
>> partitioning by "ts"?
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>>
>>
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>
>
> --
> Angular momentum makes the world go 'round.
>

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