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.klaver@aklaver.com
