Re: Partition with check constraint with "like"

[Nagaraj Raj]

 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 
 wrote:  
 
 On Fri, May 21, 2021 at 02:36:14AM +, 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 
> wrote:  
>  
>  On Fri, 21 May 2021 at 12:32, Nagaraj Raj  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.


  

Re: Partition with check constraint with "like"

[David Rowley]

On Fri, 21 May 2021 at 19:02, Nagaraj Raj  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

logical replication

[Marc Millas]

Hi,

my POC in postgres 12.(important ?)

if I setup 2 postgres clusters, and create a publication in one and a
subscription in the other,
and do on the pub an update which does not change the data (updating an
existing record with same data) then this (useless) update go through
replication.(ie consumes network ressource)

what are ways to avoid this ?
(I thought of a trigger to not execute the useless update, but I dont see
how to do this)
any ideas ?

thanks
PS: remarks about the meaning of this are off topic, thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: logical replication

[Andrew Dunstan]

On 5/21/21 8:41 AM, Marc Millas wrote:
> Hi,
>
> my POC in postgres 12.(important ?)
>
> if I setup 2 postgres clusters, and create a publication in one and a
> subscription in the other,
> and do on the pub an update which does not change the data (updating
> an existing record with same data) then this (useless) update go
> through replication.(ie consumes network ressource)
>
> what are ways to avoid this ?
> (I thought of a trigger to not execute the useless update, but I
> dont see how to do this)
> any ideas ?
>
> thanks
> PS: remarks about the meaning of this are off topic, thanks
>
>

Postgres provides exactly such a trigger. See
https://www.postgresql.org/docs/12/functions-trigger.html


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: logical replication

[Marc Millas]

perfect :-)

thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Fri, May 21, 2021 at 3:21 PM Andrew Dunstan  wrote:

>
> On 5/21/21 8:41 AM, Marc Millas wrote:
> > Hi,
> >
> > my POC in postgres 12.(important ?)
> >
> > if I setup 2 postgres clusters, and create a publication in one and a
> > subscription in the other,
> > and do on the pub an update which does not change the data (updating
> > an existing record with same data) then this (useless) update go
> > through replication.(ie consumes network ressource)
> >
> > what are ways to avoid this ?
> > (I thought of a trigger to not execute the useless update, but I
> > dont see how to do this)
> > any ideas ?
> >
> > thanks
> > PS: remarks about the meaning of this are off topic, thanks
> >
> >
>
> Postgres provides exactly such a trigger. See
> https://www.postgresql.org/docs/12/functions-trigger.html
>
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>

Re: Partition with check constraint with "like"

[Nagaraj Raj]

 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 
 wrote:  
 
 On Fri, 21 May 2021 at 19:02, Nagaraj Raj  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


  

Re: Partition with check constraint with "like"

[Vijaykumar Jain]

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







On Fri, 21 May 2021 at 22:08, Nagaraj Raj  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 <
> dgrowle...@gmail.com> wrote:
>
>
> On Fri, 21 May 2021 at 19:02, Nagaraj Raj  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

Re: Partition with check constraint with "like"

[Nagaraj Raj]

 > 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 
 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  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 
 wrote:  
 
 On Fri, 21 May 2021 at 19:02, Nagaraj Raj  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  

RE: Partition with check constraint with "like"

[Michel SALAIS]

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  
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley 
Cc : Justin Pryzby ; Pgsql-performance 

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 mailto:dgrowle...@gmail.com> > wrote: 

 

 

On Fri, 21 May 2021 at 19:02, Nagaraj Raj mailto:nagaraj...@yahoo.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

Re: Partition with check constraint with "like"

[Nagaraj Raj]

 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  
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  
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley 
Cc : Justin Pryzby ; Pgsql-performance 

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  
wrote: 

  

  

On Fri, 21 May 2021 at 19:02, Nagaraj Raj  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


  

Re: Partition with check constraint with "like"

[Nagaraj Raj]

 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 
 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  
wrote:  
 
 #yiv139250 -- filtered {}#yiv139250 filtered {}#yiv139250 filtered 
{}#yiv139250 filtered {}#yiv139250 filtered {}#yiv139250 
p.yiv139250MsoNormal, #yiv139250 li.yiv139250MsoNormal, 
#yiv139250 div.yiv139250MsoNormal 
{margin:0cm;font-size:11.0pt;font-family:sans-serif;}#yiv139250 a:link, 
#yiv139250 span.yiv139250MsoHyperlink 
{color:blue;text-decoration:underline;}#yiv139250 
span.yiv139250EmailStyle20 
{font-family:sans-serif;color:windowtext;}#yiv139250 
.yiv139250MsoChpDefault {font-size:10.0pt;}#yiv139250 filtered 
{}#yiv139250 div.yiv139250WordSection1 {}#yiv139250 
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  
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley 
Cc : Justin Pryzby ; Pgsql-performance 

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  
wrote: 

  

  

On Fri, 21 May 2021 at 19:02, Nagaraj Raj  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

Re: Partition with check constraint with "like"

[David Rowley]

On Sat, 22 May 2021 at 04:38, Nagaraj Raj  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

Re: Partition with check constraint with "like"

[David Rowley]

On Sat, 22 May 2021 at 10:59, Nagaraj Raj  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

RE: Partition with check constraint with "like"

[Michel SALAIS]

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  
Envoyé : samedi 22 mai 2021 01:28
À : 'David Rowley' ; Michel SALAIS 
Cc : 'Justin Pryzby' ; 'Pgsql-performance' 
; Michael Lewis 
Objet : Re: Partition with check constraint with "like"

 

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 mailto:nagaraj...@yahoo.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 mailto:msal...@msym.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 mailto:nagaraj...@yahoo.com> > 
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley mailto:dgrowle...@gmail.com> >
Cc : Justin Pryzby mailto:pry...@telsasoft.com> >; 
Pgsql-performance mailto:pgsql-performa...@postgresql.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 mailto:dgrowle...@gmail.com> > wrote: 

 

 

On Fri, 21 May 2021 at 19:02, Nagaraj Raj mailto:nagaraj...@yahoo.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