Thread: 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

On 06/15/2018 09:59 AM, Jeremy Finzel wrote:
> 
> 
> On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     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 <mailto:adrian.klaver@aklaver.com>
> 
> 
> Let me clarify - what I said was not accurate.  What I meant is that 
> using an UPSERT on a parent partition is not supported with range types 
> specifically because we can't create unique indexes involving 
> expressions on parent partitions:
> 
> CREATE UNIQUE INDEX ON foo (id, lower(as_of_date));
> ERROR:  unsupported UNIQUE constraint with partition key definition
> DETAIL:  UNIQUE constraints cannot be used when partition keys include 
> expressions.

Would it be possible to show all the schema involved in the above?

> 
> Workaround is of course not to use UPSERT, but we all know the 
> advantages of using UPSERT to handle concurrency and the like and to 
> make our queries simpler.  We are currently using UPSERT for many of 
> these tables, but they are not partitioned yet.
> 
> Thanks,
> Jeremy


-- 
Adrian Klaver
adrian.klaver@aklaver.com