RE: Partition with check constraint with "like"

Hi,
I am trying to create partitions on the table based on first letter of the column record  value using inherit relation & check constraint.
Somehow able to create and load the data into the tables as per my requirement.
But the problem is when querying the data on that partitioned column, it's referring to all the children's tables instead of the matching table.

create table t1(id int,name text);
 CREATE TABLE partition_tab.t1_name_null( CONSTRAINT null_check CHECK (name IS NULL)) INHERITS (t1); CREATE or replace FUNCTION partition_tab.func_t1_insert_trigger() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOFAS $BODY$DECLARE chk_cond text; c_table TEXT; c_table1 text; new_name  text; m_table1 text; BEGIN  if ( NEW.name is null) THEN INSERT into partition_tab.t1_name_null VALUES (NEW.*); elseif ( NEW.name is not null) THEN new_name:= substr(NEW.name,1,1); raise info 'new_name %',new_name;  c_table := TG_TABLE_NAME || '_' || new_name; c_table1 := 'partition_tab.' || c_table; m_table1 := ''||TG_TABLE_NAME; IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=lower(c_table)) THEN RAISE NOTICE 'values out of range partition, creating partition table: partition_tab.%',c_table;
 chk_cond := new_name||'%';  raise info 'chk_cond %',chk_cond;
 EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';

 END IF;  EXECUTE 'INSERT INTO ' || c_table1 || ' SELECT(' || m_table1 || ' ' || quote_literal(NEW) || ').* RETURNING id;'; END IF; RETURN NULL; END;$BODY$;
CREATE TRIGGER t1_trigger BEFORE INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE partition_tab.func_t1_insert_trigger()

examples: Postgres 11 | db<>fiddle

|
|
| |
Postgres 11 | db<>fiddle

Free online SQL environment for experimenting and sharing.
|

|

|

Any suggestions.

Thanks,Rj

On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> I am trying to create partitions on the table based on first letter of the column record value using inherit relation & check constraint.

You'll get much better performance out of native partitioning than you
will with the old inheritance method of doing it.

> EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';

This is a bad idea. There's a lock upgrade hazard here that could end
up causing deadlocks on INSERT. You should just create all the tables
you need beforehand.

I'd recommend you do this using RANGE partitioning. For example:

create table mytable (a text not null) partition by range (a);
create table mytable_a partition of mytable for values from ('a') to
('b'); -- note the upper bound of the range is non-inclusive.
create table mytable_b partition of mytable for values from ('b') to ('c');
insert into mytable values('alpha'),('bravo');

explain select * from mytable where a = 'alpha';
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on mytable_a mytable (cost=0.00..27.00 rows=7 width=32)
Filter: (a = 'alpha'::text)
(2 rows)

The mytable_b is not scanned.

David

Thank you. This is a great help. 
But "a" have some records with alpha and numeric. 
example :
insert into mytable values('alpha'),('bravo');
insert into mytable values('1lpha'),('2ravo');

On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> I am trying to create partitions on the table based on first letter of the column record  value using inherit relation & check constraint.

You'll get much better performance out of native partitioning than you
will with the old inheritance method of doing it.

>  EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';

This is a bad idea. There's a lock upgrade hazard here that could end
up causing deadlocks on INSERT.  You should just create all the tables
you need beforehand.

I'd recommend you do this using RANGE partitioning. For example:

create table mytable (a text not null) partition by range (a);
create table mytable_a partition of mytable for values from ('a') to
('b'); -- note the upper bound of the range is non-inclusive.
create table mytable_b partition of mytable for values from ('b') to ('c');
insert into mytable values('alpha'),('bravo');

explain select * from mytable where a = 'alpha';
                            QUERY PLAN
-------------------------------------------------------------------
Seq Scan on mytable_a mytable  (cost=0.00..27.00 rows=7 width=32)
  Filter: (a = 'alpha'::text)
(2 rows)

The mytable_b is not scanned.

David

On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote:
> Thank you. This is a great help. 
> But "a" have some records with alpha and numeric. 

So then you should make one or more partitions FROM ('1')TO('9').

> example :
> insert into mytable values('alpha'),('bravo');
> insert into mytable values('1lpha'),('2ravo');
>
>
> On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> > I am trying to create partitions on the table based on first letter of the column record  value using inherit relation & check constraint.
>
> You'll get much better performance out of native partitioning than you
> will with the old inheritance method of doing it.
>
> >  EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
>
> This is a bad idea. There's a lock upgrade hazard here that could end
> up causing deadlocks on INSERT.  You should just create all the tables
> you need beforehand.
>
> I'd recommend you do this using RANGE partitioning. For example:
>
> create table mytable (a text not null) partition by range (a);
> create table mytable_a partition of mytable for values from ('a') to
> ('b'); -- note the upper bound of the range is non-inclusive.
> create table mytable_b partition of mytable for values from ('b') to ('c');
> insert into mytable values('alpha'),('bravo');
>
> explain select * from mytable where a = 'alpha';
>                             QUERY PLAN
> -------------------------------------------------------------------
> Seq Scan on mytable_a mytable  (cost=0.00..27.00 rows=7 width=32)
>   Filter: (a = 'alpha'::text)
> (2 rows)
>
> The mytable_b is not scanned.

On Thu, May 20, 2021, 8:38 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote:
> > Thank you. This is a great help.
> > But "a" have some records with alpha and numeric.
>
> So then you should make one or more partitions FROM ('1')TO('9').
>

What about 0? Sorry.

Seriously though, this seems like a dumb question but if I wanted a
partition for each numeric digit and each alpha character (upper and
lowercase?) And wanted to avoid using a default partition, how would I use
minvalue and maxvalue and determine which partition of
A to B
B to C
...
a to b
b to c
...
0 to 1
Etc... And how to figure out the gaps between 9 and A or z and A or what?

I hope the nature of my question makes sense. What is the ordering of the
characters as far as partitioning goes? Or rather, how would I figure that
out?

>

So what about 'Z' or 'z' and 9?
I created the partitions tables FROM (A) to (B) ;FROM (B) to (C) ;
.. FROM (Y) to (Z) ;

then what would be the range of ZFROM (Z) to (?) ;
 same way for 9  On Thursday, May 20, 2021, 07:38:50 PM PDT, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote:
>  Thank you. This is a great help. 
> But "a" have some records with alpha and numeric. 

So then you should make one or more partitions FROM ('1')TO('9').

> example :
> insert into mytable values('alpha'),('bravo');
> insert into mytable values('1lpha'),('2ravo');
>
>
>    On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote: 
> 
>  On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> > I am trying to create partitions on the table based on first letter of the column record  value using inherit relation & check constraint.
>
> You'll get much better performance out of native partitioning than you
> will with the old inheritance method of doing it.
>
> >  EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
>
> This is a bad idea. There's a lock upgrade hazard here that could end
> up causing deadlocks on INSERT.  You should just create all the tables
> you need beforehand.
>
> I'd recommend you do this using RANGE partitioning. For example:
>
> create table mytable (a text not null) partition by range (a);
> create table mytable_a partition of mytable for values from ('a') to
> ('b'); -- note the upper bound of the range is non-inclusive.
> create table mytable_b partition of mytable for values from ('b') to ('c');
> insert into mytable values('alpha'),('bravo');
>
> explain select * from mytable where a = 'alpha';
>                             QUERY PLAN
> -------------------------------------------------------------------
>  Seq Scan on mytable_a mytable  (cost=0.00..27.00 rows=7 width=32)
>   Filter: (a = 'alpha'::text)
> (2 rows)
>
> The mytable_b is not scanned.

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

Hi David,
Hi,
I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)

I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table. 

postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)
create table mytable_z of mytable  for values from ('Z') to ('Z[');CREATE TABLE 
insert into mytable  values(4,'ZAR83NB');

ERROR: no partition of relation "mytable" found for rowDETAIL: Partition key of the failing row contains (name) = (ZAR83NB).SQL state: 23514

On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

just out of curiosity,
what would a typical query be ?

select * from t1 where name = somename ? == equality match // if yes,
hash partitioning may be helpful to a have reasonably balanced distribution
or
select * from t1 where name like 'some%'; ---- what would be the
distribution of rows for such queries. i mean it can return 1 row or all
rows or anything in between.

that may result in unbalanced partitioning.

then why partition at all ? 2B rows, if i go with 100KB size per row. that
would be around 200GB.

also, queries may benefit from trigram matching.
Index Columns for `LIKE` in PostgreSQL | Niall Burkley's Developer Blog
<https://niallburkley.com/blog/index-columns-for-like-in-postgres/>

<https://niallburkley.com/blog/index-columns-for-like-in-postgres/>

On Fri, 21 May 2021 at 22:08, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:

> Hi David,
>
> Hi,
>
> I am trying to create partitions on the table which have around 2BIL
> records and users will always look for the "name", its not possible to
> create a partition with a list, so we are trying to create a
> partition-based first letter of the name column. name column has a
> combination of alpha numeric values.
>
>
>
> > postgres=# select chr(ascii('z')+1) ;
> > chr
> > -----
> > {
> > (1 row)
>
> I tried as below, I'm able to create a partition table for 'Z', but it's
> not identifying partition table.
>
>
> postgres=# select chr(ascii('Z')+1) ;
> chr
> -----
> [
> (1 row)
>
> create table mytable_z of mytable for values from ('Z') to ('Z[');
> CREATE TABLE
>
> insert into mytable values(4,'ZAR83NB');
>
> ERROR: no partition of relation "mytable" found for row DETAIL: Partition
> key of the failing row contains (name) = (ZAR83NB). SQL state: 23514
>
>
>
>
>
> On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <
> dgrowleyml(at)gmail(dot)com> wrote:
>
>
> On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> > then what would be the range of Z
> > FROM (Z) to (?) ;
>
> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)
>
>
> > same way for 9
>
> postgres=# select chr(ascii('9')+1) ;
> chr
> -----
> :
> (1 row)
>
> https://en.wikipedia.org/wiki/ASCII
>
> You can also use MINVALUE and MAXVALUE to mean unbounded at either end
> of the range.
>
> But is there a particular need that you want to partition this way? It
> seems like it might be a bit painful to maintain, especially if you're
> not limiting yourself to ASCII or ANSI characters.
>
> You might want to consider HASH partitioning if you're just looking
> for a way to keep your tables and indexes to a more manageable size.
> You've not really mentioned your use case here, so it's hard to give
> any advice.
>
> There are more details about partitioning in
> https://www.postgresql.org/docs/current/ddl-partitioning.html
>
>
> David
>
>
>

--
Thanks,
Vijay
Mumbai, India

> select * from t1 where name = somename ?  == equality match  // if yes, hash partitioning may be helpful to a have reasonably balanced distribution
yes, its an equality check,

  On Friday, May 21, 2021, 12:08:25 PM PDT, Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

just out of curiosity,what would a typical query be ?
select * from t1 where name = somename ?  == equality match  // if yes, hash partitioning may be helpful to a have reasonably balanced distributionorselect * from t1 where name like 'some%';  ----  what would be the distribution of rows for such queries. i mean it can return 1 row or all rows or anything in between.                                                                            that may result in unbalanced partitioning.                                                                            then why partition at all ? 2B rows, if i go with 100KB size per row. that would be around 200GB.
also, queries may benefit from trigram matching.Index Columns for `LIKE` in PostgreSQL | Niall Burkley's Developer Blog

On Fri, 21 May 2021 at 22:08, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:

Hi David,
Hi,
I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)

I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table. 

postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)
create table mytable_z of mytable  for values from ('Z') to ('Z[');CREATE TABLE 
insert into mytable  values(4,'ZAR83NB');

ERROR: no partition of relation "mytable" found for rowDETAIL: Partition key of the failing row contains (name) = (ZAR83NB).SQL state: 23514

On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

--
Thanks,VijayMumbai, India

Hi

I don’t discuss here the choice itself but this is not correct:

create table mytable_z of mytable for values from ('Z') to ('Z[');

It should be

create table mytable_z of mytable for values from ('Z') to ('[')

Michel SALAIS

De : Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowleyml(at)gmail(dot)com>
Cc : Justin Pryzby <pryzby(at)telsasoft(dot)com>; Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Objet : Re: Partition with check constraint with "like"

Hi David,

Hi,

I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)

I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table.

postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)

create table mytable_z of mytable for values from ('Z') to ('Z[');

CREATE TABLE

insert into mytable values(4,'ZAR83NB');

ERROR: no partition of relation "mytable" found for row DETAIL: Partition key of the failing row contains (name) = (ZAR83NB). SQL state: 23514

On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com> > wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)com> > wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

Hi, 
This is also not working,

create table mytable_z partition of mytable for values from ('Z') to ('[')partition by range(id);

ERROR: empty range bound specified for partition "mytable_z"DETAIL: Specified lower bound ('Z') is greater than or equal to upper bound ('[').SQL state: 42P17

DB running on version PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit
On Friday, May 21, 2021, 02:00:38 PM PDT, Michel SALAIS <msalais(at)msym(dot)fr> wrote:

#yiv0089608923 #yiv0089608923 -- _filtered {} _filtered {} _filtered {} _filtered {} _filtered {}#yiv0089608923 #yiv0089608923 p.yiv0089608923MsoNormal, #yiv0089608923 li.yiv0089608923MsoNormal, #yiv0089608923 div.yiv0089608923MsoNormal {margin:0cm;font-size:11.0pt;font-family:sans-serif;}#yiv0089608923 a:link, #yiv0089608923 span.yiv0089608923MsoHyperlink {color:blue;text-decoration:underline;}#yiv0089608923 span.yiv0089608923EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv0089608923 .yiv0089608923MsoChpDefault {font-size:10.0pt;} _filtered {}#yiv0089608923 div.yiv0089608923WordSection1 {}#yiv0089608923
Hi

I don’t  discuss here the choice itself but this is not correct:

create table mytable_z of mytable  for values from ('Z') to ('Z[');

It should be

create table mytable_z of mytable  for values from ('Z') to ('[')

Michel SALAIS

De : Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowleyml(at)gmail(dot)com>
Cc : Justin Pryzby <pryzby(at)telsasoft(dot)com>; Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Objet : Re: Partition with check constraint with "like"

Hi David,

Hi,

I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)

I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table. 

postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)

create table mytable_z of mytable  for values from ('Z') to ('Z[');

CREATE TABLE 

insert into mytable  values(4,'ZAR83NB');

ERROR: no partition of relation "mytable" found for row DETAIL: Partition key of the failing row contains (name) = (ZAR83NB). SQL state: 23514

On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

sorry, forgot to attach the test cases.Postgres 13 | db<>fiddle

|
|
| |
Postgres 13 | db<>fiddle

Free online SQL environment for experimenting and sharing.
|

|

|

On Friday, May 21, 2021, 03:59:18 PM PDT, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:

Hi, 
This is also not working,

create table mytable_z partition of mytable for values from ('Z') to ('[')partition by range(id);

ERROR: empty range bound specified for partition "mytable_z"DETAIL: Specified lower bound ('Z') is greater than or equal to upper bound ('[').SQL state: 42P17

DB running on version PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit
On Friday, May 21, 2021, 02:00:38 PM PDT, Michel SALAIS <msalais(at)msym(dot)fr> wrote:

#yiv1392522220 -- filtered {}#yiv1392522220 filtered {}#yiv1392522220 filtered {}#yiv1392522220 filtered {}#yiv1392522220 filtered {}#yiv1392522220 p.yiv1392522220MsoNormal, #yiv1392522220 li.yiv1392522220MsoNormal, #yiv1392522220 div.yiv1392522220MsoNormal {margin:0cm;font-size:11.0pt;font-family:sans-serif;}#yiv1392522220 a:link, #yiv1392522220 span.yiv1392522220MsoHyperlink {color:blue;text-decoration:underline;}#yiv1392522220 span.yiv1392522220EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv1392522220 .yiv1392522220MsoChpDefault {font-size:10.0pt;}#yiv1392522220 filtered {}#yiv1392522220 div.yiv1392522220WordSection1 {}#yiv1392522220
Hi

I don’t  discuss here the choice itself but this is not correct:

create table mytable_z of mytable  for values from ('Z') to ('Z[');

It should be

create table mytable_z of mytable  for values from ('Z') to ('[')

Michel SALAIS

De : Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowleyml(at)gmail(dot)com>
Cc : Justin Pryzby <pryzby(at)telsasoft(dot)com>; Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Objet : Re: Partition with check constraint with "like"

Hi David,

Hi,

I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)

I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table. 

postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)

create table mytable_z of mytable  for values from ('Z') to ('Z[');

CREATE TABLE 

insert into mytable  values(4,'ZAR83NB');

ERROR: no partition of relation "mytable" found for row DETAIL: Partition key of the failing row contains (name) = (ZAR83NB). SQL state: 23514

On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David

On Sat, 22 May 2021 at 04:38, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

Going by the description of your use case, I think HASH partitioning
might be a better option for you. It'll certainly be less painful to
initially set up and maintain.

Here's an example:

create table mytable (a text) partition by hash(a);
create table mytable0 partition of mytable for values with(modulus 10,
remainder 0);
create table mytable1 partition of mytable for values with(modulus 10,
remainder 1);
create table mytable2 partition of mytable for values with(modulus 10,
remainder 2); --etc

Change the modulus to the number of partitions you want and ensure you
create a partition for each modulus. In this case, it would be 0 to 9.

David

On Sat, 22 May 2021 at 10:59, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> ERROR: empty range bound specified for partition "mytable_z" DETAIL: Specified lower bound ('Z') is greater than or equal to upper bound ('['). SQL state: 42P17

It looks like '[' does not come after 'Z' in your collation.

David

Hi,

Then we must know what is your collation…

What is the collation of your database?

select datname, pg_catalog.pg_encoding_to_char(encoding) "encoding", datcollate, datctype

from pg_database;

It is also possible to define an explicit collation for the column. You can have it when you describe the table…

But I think like others have already said that this is perhaps not the right choice.

Michel SALAIS

De : Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
Envoyé : samedi 22 mai 2021 01:28
À : 'David Rowley' <dgrowleyml(at)gmail(dot)com>; Michel SALAIS <msalais(at)msym(dot)fr>
Cc : 'Justin Pryzby' <pryzby(at)telsasoft(dot)com>; 'Pgsql-performance' <pgsql-performance(at)postgresql(dot)org>; Michael Lewis <mlewis(at)entrata(dot)com>
Objet : Re: Partition with check constraint with "like"

sorry, forgot to attach the test cases.

Postgres 13 | db <https://dbfiddle.uk/?rdbms=postgres_13&fiddle=602350db327ee6215837bbf48f0763f8> <>fiddle

Postgres 13 | db<>fiddle

Free online SQL environment for experimenting and sharing.

On Friday, May 21, 2021, 03:59:18 PM PDT, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)com> > wrote:

Hi,

This is also not working,

create table mytable_z partition of mytable for values from ('Z') to ('[')

partition by range(id);

ERROR: empty range bound specified for partition "mytable_z" DETAIL: Specified lower bound ('Z') is greater than or equal to upper bound ('['). SQL state: 42P17

DB running on version PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit

On Friday, May 21, 2021, 02:00:38 PM PDT, Michel SALAIS <msalais(at)msym(dot)fr <mailto:msalais(at)msym(dot)fr> > wrote:

Hi

I don’t discuss here the choice itself but this is not correct:

create table mytable_z of mytable for values from ('Z') to ('Z[');

It should be

create table mytable_z of mytable for values from ('Z') to ('[')

Michel SALAIS

De : Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)com> >
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com> >
Cc : Justin Pryzby <pryzby(at)telsasoft(dot)com <mailto:pryzby(at)telsasoft(dot)com> >; Pgsql-performance <pgsql-performance(at)postgresql(dot)org <mailto:pgsql-performance(at)postgresql(dot)org> >
Objet : Re: Partition with check constraint with "like"

Hi David,

Hi,

I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

> postgres=# select chr(ascii('z')+1) ;
> chr
> -----
> {
> (1 row)

I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table.

postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)

create table mytable_z of mytable for values from ('Z') to ('Z[');

CREATE TABLE

insert into mytable values(4,'ZAR83NB');

ERROR: no partition of relation "mytable" found for row DETAIL: Partition key of the failing row contains (name) = (ZAR83NB). SQL state: 23514

On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com> > wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)com> > wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;

postgres=# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)

> same way for 9

postgres=# select chr(ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCII

You can also use MINVALUE and MAXVALUE to mean unbounded at either end
of the range.

But is there a particular need that you want to partition this way? It
seems like it might be a bit painful to maintain, especially if you're
not limiting yourself to ASCII or ANSI characters.

You might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable size.
You've not really mentioned your use case here, so it's hard to give
any advice.

There are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.html

David