Re: [HACKERS] Range Partitioning behaviour - query

Hi,

On 2017/02/23 11:55, Venkata B Nagothi wrote:
> Hi Hackers,
>
> I have noticed the following behaviour in range partitioning which i felt
> is not quite correct (i missed reporting this) -
>
> I have tested by creating a date ranged partition.
>
> I created the following table.
>
> db03=# CREATE TABLE orders (
>         o_orderkey INTEGER,
>         o_custkey INTEGER,
>         o_orderstatus CHAR(1),
>         o_totalprice REAL,
>         o_orderdate DATE,
>         o_orderpriority CHAR(15),
>         o_clerk CHAR(15),
>         o_shippriority INTEGER,
>         o_comment VARCHAR(79)) partition by range (o_orderdate);
> CREATE TABLE
>
> Created the following partitioned tables :
>
>
> db03=# CREATE TABLE orders_y1992
>     PARTITION OF orders FOR VALUES FROM ('1992-01-01') TO ('1992-12-31');
>                   CREATE TABLE
>
> db03=# CREATE TABLE orders_y1993
>     PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('*1993-12-31'*);
> CREATE TABLE
>
> db03=# CREATE TABLE orders_y1994
>    PARTITION OF orders FOR VALUES FROM ('1994-01-01') TO ('1994-12-31');
> CREATE TABLE
>
>
> The rows with the date "1993-12-31" gets rejected as shown below -
>
> db03=# copy orders from '/data/orders.csv' delimiter '|';
> ERROR:  no partition of relation "orders" found for row
> DETAIL:  Failing row contains (353, 8878, F, 273342, *1993-12-31*, 5-LOW
>        , Clerk#000002241, 0,  quiet ideas sleep. even instructions cajole
> slyly. silently spe).
> CONTEXT:  COPY orders, line 89: "353|8878|F|273342|*1993-12-31*|5-LOW
>    |Clerk#000002241|0| quiet ideas sleep. even instructions..."
>
> I would want the partition "orders_y1993" to accept all the rows with the
> date 1993-12-31.

[ ... ]

> Am i missing anything here ?

Upper bound of a range partition is an exclusive bound.  A note was added
recently to the CREATE TABLE page to make this clear.

https://www.postgresql.org/docs/devel/static/sql-createtable.html