Re: Doubt with [ RANGE partition with TEXT datatype ]

On Mon, Apr 19, 2021 at 2:16 PM Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

> Hi Prabhat,
>
> On Mon, Apr 19, 2021 at 5:13 PM Prabhat Sahu
> <prabhat(dot)sahu(at)enterprisedb(dot)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