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