Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lkKOZ-0005JX-VI for pgsql-performance@arkaria.postgresql.org; Sat, 22 May 2021 05:41:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lkKOY-0000P5-W7 for pgsql-performance@arkaria.postgresql.org; Sat, 22 May 2021 05:41:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lkKOY-0000Ou-FT for pgsql-performance@lists.postgresql.org; Sat, 22 May 2021 05:41:54 +0000 Received: from mout.kundenserver.de ([212.227.126.133]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lkKOV-0004IS-2z for pgsql-performance@postgresql.org; Sat, 22 May 2021 05:41:53 +0000 Received: from ANAT ([77.153.110.36]) by mrelayeu.kundenserver.de (mreue011 [212.227.15.168]) with ESMTPSA (Nemesis) id 1MhULz-1lGCV81YCh-00ed0A; Sat, 22 May 2021 07:41:45 +0200 From: "Michel SALAIS" To: "'Nagaraj Raj'" , "'David Rowley'" Cc: "'Justin Pryzby'" , "'Pgsql-performance'" , "'Michael Lewis'" References: <87840670.92362.1621557131080.ref@mail.yahoo.com> <87840670.92362.1621557131080@mail.yahoo.com> <1347719485.144821.1621564574978@mail.yahoo.com> <20210521023836.GH3676@telsasoft.com> <81358482.168153.1621580571840@mail.yahoo.com> <2126516115.347941.1621615110621@mail.yahoo.com> <005201d74e84$4ee9be30$ecbd3a90$@msym.fr> <2032281808.469685.1621637941028@mail.yahoo.com> <1954965228.496968.1621639708945@mail.yahoo.com> In-Reply-To: <1954965228.496968.1621639708945@mail.yahoo.com> Subject: RE: Partition with check constraint with "like" Date: Sat, 22 May 2021 07:41:43 +0200 Message-ID: <006701d74ecd$2662f280$7328d780$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0068_01D74EDD.E9ED4920" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIibzL7vrxVSqYOcIluBciLNTJhZwEu59DIAcmU930CsVRv9gJC+QZAAk+y9jIBs0VwJAKAkuwhAumnMIwCN2c7aQHEjTX3qa4LIFA= Content-Language: fr X-Provags-ID: V03:K1:7tu+MKHjcSdkAOl063ZI2OiJHZ/M4qSVikjued2CzyuqtYRmW03 mXM0mg+IVlkCzTu+MOJBfiUNcb+F8zKPLDSoca+ltjStcR9nDkhG9TIZbIyTx534xwWqx1U NTE+yar3K9kHBK4zrNIE3I1HonVd1s26ntDwcAO4aZKwyU/K1gjVgxH3YJV8BoP80rVFGr8 QQLb46JW/krLm3g5BM/pQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:3KPixMPnq/Q=:iNlUUvWjDxNaxA9+vIbZAd 7nvQH8QHKXf0m3/BZxsVt2mEie+/a9R9+3H8R0a383EDSv42HdUrLKwcpnjrU61AXKPEGkGEp NMdxNW6h/yxpjdGpNdsX547oFCtCx9y3bQ8eqo7KZJoDRMvCLxsxiRbiHk22xnsm6sV6YGQ0K LhcMmwgmAcmwLiZibWum0G8i4NruwEGW8/mNKxcgArxWNHjXktpmo3zpHOoSqHH2W0NNy8+n3 t+spte8l9NDmR39DGPVRdgYleM0aGXZI/JWu2n4wkVVw29S+vEh1l+X0K8FMqQQ6i+1htDNcg 5Xa1Ub9DMMDhJdEKpLjm5NJWWVYHMDP4BFqWV3bpkoly1l6EwQBmKnvR8l6Tt/nVgN+BVL+gL Jsoyzt1mB0KQd/NXa68geHIfrc/u6a4BWXMYA2Eoe1ptOVaegnAU9yGI3yEoDgs34ObjNN25T Hr+7R74Cu3i3sFPZM1j4/V335UY4WOSbWNB2ilntxDWZrTrwQr/2EgSCcCI5rp+UguAtzxCCQ SHT1u10SgX9aJB5cSeAsrc= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0068_01D74EDD.E9ED4920 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi, =20 Then we must know what is your collation=E2=80=A6 What is the collation of your database? =20 select datname, pg_catalog.pg_encoding_to_char(encoding) "encoding", = datcollate, datctype from pg_database; =20 It is also possible to define an explicit collation for the column. You = can have it when you describe the table=E2=80=A6 =20 But I think like others have already said that this is perhaps not the = right choice. =20 Michel SALAIS De : Nagaraj Raj =20 Envoy=C3=A9 : samedi 22 mai 2021 01:28 =C3=80 : 'David Rowley' ; Michel SALAIS = Cc : 'Justin Pryzby' ; 'Pgsql-performance' = ; Michael Lewis Objet : Re: Partition with check constraint with "like" =20 sorry, forgot to attach the test cases. Postgres 13 | db = <>fiddle =20 =09 Postgres 13 | db<>fiddle Free online SQL environment for experimenting and sharing. =20 =20 =20 On Friday, May 21, 2021, 03:59:18 PM PDT, Nagaraj Raj = > wrote:=20 =20 =20 Hi,=20 =20 This is also not working, =20 =20 create table mytable_z partition of mytable for values from ('Z') to = ('[') partition by range(id); =20 =20 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 =20 =20 DB running on version PostgreSQL 11.6, compiled by Visual C++ build = 1800, 64-bit =20 On Friday, May 21, 2021, 02:00:38 PM PDT, Michel SALAIS > wrote:=20 =20 =20 Hi I don=E2=80=99t discuss here the choice itself but this is not correct: create table mytable_z of mytable for values from ('Z') to ('Z['); =20 It should be create table mytable_z of mytable for values from ('Z') to ('[') =20 Michel SALAIS =20 De : Nagaraj Raj >=20 Envoy=C3=A9 : vendredi 21 mai 2021 18:39 =C3=80 : David Rowley > Cc : Justin Pryzby = >; Pgsql-performance > Objet : Re: Partition with check constraint with "like" =20 Hi David, =20 Hi, =20 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. =20 =20 =20 > postgres=3D# select chr(ascii('z')+1) ; > chr > ----- > { > (1 row) =20 I tried as below, I'm able to create a partition table for 'Z', but it's = not identifying partition table.=20 =20 =20 postgres=3D# select chr(ascii('Z')+1) ; chr ----- [ (1 row) =20 create table mytable_z of mytable for values from ('Z') to ('Z['); CREATE TABLE=20 =20 insert into mytable values(4,'ZAR83NB'); =20 ERROR: no partition of relation "mytable" found for row DETAIL: = Partition key of the failing row contains (name) =3D (ZAR83NB). SQL = state: 23514 =20 =20 =20 =20 =20 On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley = > wrote:=20 =20 =20 On Fri, 21 May 2021 at 19:02, Nagaraj Raj > wrote: > then what would be the range of Z > FROM (Z) to (?) ; postgres=3D# select chr(ascii('z')+1) ; chr ----- { (1 row) > same way for 9 postgres=3D# 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 ------=_NextPart_000_0068_01D74EDD.E9ED4920 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi,

 

Then we must know what is your = collation=E2=80=A6

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=E2=80=A6

 

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

 

Michel = SALAIS

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

 

sorry, = forgot to attach the test cases.

 =

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.sf@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 <msalais@msym.fr> wrote: =

 

 

Hi

I don=E2=80=99t  discuss here the choice itself but this is not = correct:

c= reate table mytable_z of myt= able&= nbsp; for values from ('Z') to ('Z[');

 

It should be

c= reate table mytable_z of myt= able&= nbsp; for values from ('Z') to ('[')

 

Mi= chel SALAIS

 

De : Nagaraj Raj <nagaraj.sf@yahoo.com> =
Envoy=C3=A9 : vendredi 21 mai 2021 = 18:39
=C3=80 : David Rowley <dgrowleyml@gmail.com>
C= c : Justin Pryzby <pryzby@telsasoft.com>; = Pgsql-performance <pgsql-performance@postgr= esql.org>
Objet : Re: Partition with check = constraint with "like"

 

H= i David,

&= nbsp;

H= i,

&= nbsp;

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.

&= nbsp;

&= nbsp;

&= nbsp;

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

&= nbsp;

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

&= nbsp;

&= nbsp;

p= ostgres=3D# select chr(ascii('Z')+1) ;
chr
-----
[
(1 = row)

&= nbsp;

c= reate table mytable_z of myt= able&= nbsp; for values from ('Z') to ('Z[');

C= REATE TABLE 

&= nbsp;

i= nsert into myt= able&= nbsp; values(4,'ZAR83NB');

&= nbsp;

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

&= nbsp;

&= nbsp;

&= nbsp;

&= nbsp;

&= nbsp;

O= n Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml@gmail.com> wrote:

&= nbsp;

&= nbsp;

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

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


> same way for 9

postgres=3D# 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-partitionin= g.html

<= br>
David

=
= ------=_NextPart_000_0068_01D74EDD.E9ED4920--