Re: Partitioning with range types

On 06/15/2018 08:26 AM, Jeremy Finzel wrote:
> Several months ago we had some detailed discussions about whether to use
> separate date columns to indicate a date range, or to use the daterange
> data type.  We opted for the latter because this type is specifically
> designed for this use case - a table that has a range of valid dates for
> the data it contains.  It also has some great operators and functions.
>
> But I recently discovered that daterange is not supported in any way as
> a partition key because it depends on an expression.  I was excited

A quick test:

Postgres 10.4

create table dr_partition(id integer, dr daterange) PARTITION BY LIST(dr);

\d dr_partition
Table "public.dr_partition"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | |
dr | daterange | | |
Partition key: LIST (dr)

create table dr_1 PARTITION OF dr_partition FOR VALUES IN ('[06/01/2018,
06/30/2018]');

\d dr_1
Table "public.dr_1"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | |
dr | daterange | | |
Partition of: dr_partition FOR VALUES IN ('[2018-06-01,2018-07-01)')

> about this possibility in pg11 with unique constraints on the parent
> table, but now it appears it may have instead been to our advantage if
> we had two separate date columns instead, so that we could use UPSERT
> transparently for date-ranged tables.
>
> Is there any possibility of this feature coming for range types, or, if
> we really want to partition using daterange, should we look instead at
> two separate date columns?
>
> Thanks,
> Jeremy

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com