Re: [HACKERS] Range Partitioning behaviour - query
On Fri, Feb 24, 2017 at 12:38 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote < > langote_amit...@lab.ntt.co.jp> wrote: > >> On 2017/02/24 8:38, Venkata B Nagothi wrote: >> > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote: >> >> 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. >> >> Hmm, TO sounds like it implies inclusive. >> > > I think most common usage of the word ends up being inclusive but the > word itself doesn't really care. > > Dictionary.com has a good example: > > "We work from nine to five." - you leave at the beginning of the 5 o'clock > hour (I'm going for casual usage here) > True. > Since our implementation of ranges is half-open the usage here is > consistent with that concept. That it doesn't match BETWEEN is actually > somewhat nice since you can use ranges for half-open and BETWEEN if you > want to be concise with fully-closed endpoints. But it is one more thing > to remember. > Agreed. Regards, Venkata B N Database Consultant
Re: [HACKERS] Range Partitioning behaviour - query
On Fri, Feb 24, 2017 at 1:01 PM, Amit Langotewrote: > On 2017/02/24 10:38, David G. Johnston wrote: > > On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote < > langote_amit...@lab.ntt.co.jp > >> wrote: > > > >> On 2017/02/24 8:38, Venkata B Nagothi wrote: > >>> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote: > 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. > >> > >> Hmm, TO sounds like it implies inclusive. > >> > > > > I think most common usage of the word ends up being inclusive but the > word > > itself doesn't really care. > > > > Dictionary.com has a good example: > > > > "We work from nine to five." - you leave at the beginning of the 5 > o'clock > > hour (I'm going for casual usage here) > > Thanks for that example. > > One problem I've seen people mention is one of cognitive dissonance of > having to define partition_y2013 as FROM ('2013-01-01') TO ('2014-01-01'), > given that that's the only way to get what one needs. But we concluded > that that's a reasonable compromise. > Agreed. I do see the similar approach adopted across other traditional RDBMS products as well. Regards, Venkata B N Database Consultant
Re: [HACKERS] Range Partitioning behaviour - query
On 2017/02/24 10:38, David G. Johnston wrote: > On Thu, Feb 23, 2017 at 6:17 PM, Amit Langote> wrote: > >> On 2017/02/24 8:38, Venkata B Nagothi wrote: >>> On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote: 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. >> >> Hmm, TO sounds like it implies inclusive. >> > > I think most common usage of the word ends up being inclusive but the word > itself doesn't really care. > > Dictionary.com has a good example: > > "We work from nine to five." - you leave at the beginning of the 5 o'clock > hour (I'm going for casual usage here) Thanks for that example. One problem I've seen people mention is one of cognitive dissonance of having to define partition_y2013 as FROM ('2013-01-01') TO ('2014-01-01'), given that that's the only way to get what one needs. But we concluded that that's a reasonable compromise. > Since our implementation of ranges is half-open the usage here is > consistent with that concept. That it doesn't match BETWEEN is actually > somewhat nice since you can use ranges for half-open and BETWEEN if you > want to be concise with fully-closed endpoints. But it is one more thing > to remember. Agreed. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Partitioning behaviour - query
On Thu, Feb 23, 2017 at 6:17 PM, Amit Langotewrote: > On 2017/02/24 8:38, Venkata B Nagothi wrote: > > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote: > >> 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. > > Hmm, TO sounds like it implies inclusive. > I think most common usage of the word ends up being inclusive but the word itself doesn't really care. Dictionary.com has a good example: "We work from nine to five." - you leave at the beginning of the 5 o'clock hour (I'm going for casual usage here) Since our implementation of ranges is half-open the usage here is consistent with that concept. That it doesn't match BETWEEN is actually somewhat nice since you can use ranges for half-open and BETWEEN if you want to be concise with fully-closed endpoints. But it is one more thing to remember. David J.
Re: [HACKERS] Range Partitioning behaviour - query
On 2017/02/24 8:38, Venkata B Nagothi wrote: > On Thu, Feb 23, 2017 at 3:14 PM, Amit Langote wrote: >> 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. Hmm, TO sounds like it implies inclusive. > Also, there are no options like "<" or "LESS THAN" clauses available. So, > "TO" translates to "<". That is what i wanted to confirm. Yes, that's it. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Partitioning behaviour - query
On Thu, Feb 23, 2017 at 3:14 PM, Amit Langotewrote: > 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#02241, 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#02241|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
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#02241, 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#02241|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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers