Postgres Pro
Downloads
Home   >   mailing lists

Thread: Doubt with [ RANGE partition with TEXT datatype ]

Doubt with [ RANGE partition with TEXT datatype ]

From
Prabhat Sahu
Date:
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

If this behavior is expected, Kindly let me know, how to represent the range from '5' to '10' with text datatype column?
Is there any specific restriction for RANGE PARTITION table with TEXT datatype column?

Similar test scenario is working fine with INTEGER datatype.

--

With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com

Re: Doubt with [ RANGE partition with TEXT datatype ]

From
Amit Langote
Date:
Hi Prabhat,

On Mon, Apr 19, 2021 at 5:13 PM Prabhat Sahu
<prabhat.sahu@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'.


--
Amit Langote
EDB: http://www.enterprisedb.com

Re: Doubt with [ RANGE partition with TEXT datatype ]

From
Prabhat Sahu
Date:

On Mon, Apr 19, 2021 at 2:16 PM Amit Langote <amitlangote09@gmail.com> wrote:
Hi Prabhat,

On Mon, Apr 19, 2021 at 5:13 PM Prabhat Sahu
<prabhat.sahu@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