On Mon, Apr 19, 2021 at 2:16 PM Amit Langote <amitlangot...@gmail.com> wrote:
> Hi Prabhat, > > On Mon, Apr 19, 2021 at 5:13 PM Prabhat Sahu > <prabhat.s...@enterprisedb.com> wrote: > > > > Hi All, > > > > Please help me out with my doubt in RANGE partition with TEXT datatype: > > > > postgres=# create table tab1 (col1 text) PARTITION BY RANGE (col1); > > CREATE TABLE > > > > postgres=# create table p1 (col1 text); > > CREATE TABLE > > > > -- Partition with range from '5' to '10' shows error: > > postgres=# alter table tab1 attach partition p1 for values from ('5') to > ('10'); > > ERROR: empty range bound specified for partition "p1" > > LINE 1: ...r table tab1 attach partition p1 for values from ('5') to > ('... > > ^ > > DETAIL: Specified lower bound ('5') is greater than or equal to upper > bound ('10'). > > > > -- Whereas, partition with range from '5' to '9' is working fine as > below: > > postgres=# alter table tab1 attach partition p1 for values from ('5') to > ('9'); > > ALTER TABLE > > Well, that is how comparing text values works. If you are expecting > the comparisons to follow numerical rules, use a numeric data type. > > > If this behavior is expected, Kindly let me know, how to represent the > range from '5' to '10' with text datatype column? > > Don't know why you want to use the text type for the column and these > particular values for the partitions bounds, but one workaround would > be to use '05' instead of '5'. > While testing on some PG behavior, I came across such a scenario/doubt. Thank you Amit for the clarification. -- With Regards, Prabhat Kumar Sahu EnterpriseDB: http://www.enterprisedb.com