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@xxxxxxxxxxx
