Re: Range Partitioning behaviour - query

On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> 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

Thanks. Actually, my confusion was that the upper bound value would be
included when "TO" clause is used in the syntax.
Also, there are no options like "<" or "LESS THAN" clauses available. So,
"TO" translates to "<". That is what i wanted to confirm.

Regards,

Venkata B N
Database Consultant