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

So do the following instead:

CREATE TABLE orders_y1993
PARTITION OF orders FOR VALUES FROM ('1993-01-01') TO ('1994-01-01');

Thanks,
Amit