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 1lkCFz-0005Ef-A3 for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 21:00:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lkCFy-00049h-3d for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 21:00:30 +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 1lkCFx-00048N-Ia for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 21:00:29 +0000 Received: from mout.kundenserver.de ([217.72.192.74]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lkCFu-0000W8-Ly for pgsql-performance@postgresql.org; Fri, 21 May 2021 21:00:28 +0000 Received: from ANAT ([77.153.110.36]) by mrelayeu.kundenserver.de (mreue108 [212.227.15.184]) with ESMTPSA (Nemesis) id 1M9Fvl-1lqUqp0ZV9-006NdL; Fri, 21 May 2021 23:00:20 +0200 From: "Michel SALAIS" To: "'Nagaraj Raj'" , "'David Rowley'" Cc: "'Justin Pryzby'" , "'Pgsql-performance'" 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> In-Reply-To: <2126516115.347941.1621615110621@mail.yahoo.com> Subject: RE: Partition with check constraint with "like" Date: Fri, 21 May 2021 23:00:18 +0200 Message-ID: <005201d74e84$4ee9be30$ecbd3a90$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0053_01D74E95.12765EC0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIibzL7vrxVSqYOcIluBciLNTJhZwEu59DIAcmU930CsVRv9gJC+QZAAk+y9jIBs0VwJAKAkuwhqeSmXEA= Content-Language: fr X-Provags-ID: V03:K1:BORvwDGOkj+1awbX3wLqQFK1ipP0vkHYRrEeem60PaMKiEERIua CAiE5JZrREUan7CrjhGclM55HS+3wEKdwkCBi+vAM2J6ku9Truq27Ibiyrghq2fgxx/hMjO uPWPn6/c9NHS2tKSxa0dEXjZ/Fu/L/nA0vTemqPtBJFLLVb+22tDFeZ15QxESbrcbGJ3V+9 N8SkqlMWnA+YuoVVkNLIw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:5YvEbioM3mI=:uEolfWfIrUa3amzWiHm4+E 6FuZHOrjAr8B5Se1ZaB+IHXFMUxv8NdKHJuiV2d4sHkhsgyu94KwOYpxnutBD/qRa+AshH0Ib 5urIrntv1BWbWJ8ojq+77QTzYcWchA3Sp9EveyaPK338NF/1vCebTN7EJza1EvH1SI26T/Bjq TVSJhm0+NuLy/42rmtZbIVsvQ6UkHBeZkQlF7aHL6dKcb1c1Ti+fnBDioWf6wo9VEWvyyG/y5 Hai4Ix9kpl7XraMxv+pdLQ9QhA+Pn4MUX63nYEB9zQ4QOIsDMqZvcdWTWzqLYbzQYCM0phVwR zk5TiOTYvCcv+ooE1MENrLvVkJ4wkY+che2+3uzbxPhsHNM7uDcWwWZr1N12r7H0vQAcAM75T 6qx+oNNXG1LUKwEzbuB4MLB74vgcumDIK3CmwM2sxr8kIToF+9AYsm8zW3qVNYzqgI0/vskkQ 1us+85O9723LXIcQeRS/Nd7ClRoZMSiT6InIXTRmxdoN1ibIIQcD4NjikDjajD4heTadV1p+X Lq9e5NOFwn6WkwjuAD45P0= 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_0053_01D74E95.12765EC0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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_0053_01D74E95.12765EC0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi

I don=E2=80=99t =C2=A0discuss 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.sf@yahoo.com>
Envoy=C3=A9 : vendredi 21 = mai 2021 18:39
=C3=80 : David Rowley = <dgrowleyml@gmail.com>
Cc : Justin Pryzby = <pryzby@telsasoft.com>; Pgsql-performance = <pgsql-performance@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=3D# 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=3D= # 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) =3D (ZAR83NB). SQL state: 23514=

 =

 =

 =

 =

 =

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

 

 

On 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



David

------=_NextPart_000_0053_01D74E95.12765EC0--