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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers