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 1lkE6o-00013g-Fz for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 22:59:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lkE6n-0003ET-Ey for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 22:59:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lkE6n-0003EL-4h for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 22:59:09 +0000 Received: from sonic306-22.consmr.mail.ne1.yahoo.com ([66.163.189.84]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lkE6j-0007cm-U9 for pgsql-performance@postgresql.org; Fri, 21 May 2021 22:59:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621637942; bh=L6LwceaJQpdOUf6845qCGsYCDiAoh7DIJvcP/SKa8q0=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=bEBjsvRbeifthMG+TJ/MoZXU8/Q3BL/YFD2QpCmm7bfNnlvv6aqwtH0mJRCY2pOJN8SrmOT5C1ZimD5YteI832QKhuuX63rRPnuTZaV0Zimu56uJgukRRR3sMuXE5lP1x05BKmebhRYoZJDGoPZ8OJP/wVbK+6r/k1e3UFaLzRIpJN8lQwyn8vQVnT4HQurkxw/1ZOA3EO1b3+jhmS4X9hF59o3L7I38+n5SEKXwThwmAyFRbCRpCGbFcCO6eL3yU2C4IhRUuwuWIYVsy45EY8IXKiE0fZYe/KKqLKgNU0UMbvf0LL+cuKF8MGlgTAph7s7a2pWnFPNIWGhvhSgbgA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621637942; bh=k5hVLUs4vz3f+nPrNLn3Nj2LMgnME+tQHNjusAsPe5t=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=uHiH3pFjsu8uq1+Dmj8pHMUp/lC/QRgkYlJGEVIMFlsZrS92UgKvY43sq5l6/ByDBOze5FNYxeOlSa/BJFG8+iWcRqeNMTPlbQ6Etz75B+8MLx7E/4bOrcpeftP4yOuo9SFXmvroV/eyIkrmv54vhzpOxoC4Xv4mXsYNMOEzEtGu0umi84uiHkFE6fKoXHUCmwJNSJFN7hVT0D8MWr/QVOHOoJoXOamkzyDSLDrRjtBQTt949cS46BUzwMCPnnAEYsI76+o6xgeZ/M7l+2TK33g1u3frLAc8NqSGn8yHBDLg5M1Zt4ixIWngZ+VlPmExKlfI5aYIYt8JyqJeN5lXIQ== X-YMail-OSG: QwHzBMcVM1k_TMC6Jjr4oPvJCcQwVspyjGrAgvBxIgJbLus60IwcoA1OTfHrYRg deondu.hDFZORMCb5D5lrknjlr1kUNFzZFRam2Rb7DD0bKOiGSR71AvGajA7olA.yQvORr418eRi 76.SZuXGV7YWzTTRTT2A.tsZeQRozLqyqgVSaISwhDPni3YDVxeCTcLK4ujTRpa1aBXlwgXPVFCS Ao1nO.SJ.m9W48sct7qrqL4lrd2pPkdKvegDUV.6uHx8qSGNSLde6az3zr3odQxdPhTBvB_GcEh8 c3s0cPHq6z6GWKF3TCooG1xlsM4TuAllIY91Nu7NqoMHVpy7PpE56YfJ4EPG5hCP19rJuOhQy9Jm xsf44oXuWpIzlyd2Lmsn9p1QOJT1xe5Xt2r8KIkX1KCIt7k655rPgHH.QNXXiJxHdnJrQNheE0Un vePxT3H7ohBeGcXRCttkFpduoYZe6VNfF4GIBX_iE_RbsoOcfaWapKMhT3wxJLnuQ9DlLQ1pB3sD QMGwbrYzol3n6X5fLkKHrZ.wqgO.Dvk5MglpP3ZMFzQCzmplhZVUC.XdwXJKnHh2oHHUf2aON.u3 oaiZiTV3ol.AwSKwXWRfNtG3.He4kS7TO9k78QqVvRg2Qgz5.JWxvQFsbwPqj1SaLVpRdrFXWhVj iDNYHRX2zrikmOjaxJLJSR.ya24MFhzXszKOUoJ34AO7WgQbhK1473.ScD3sYXY1RlG9wMNohzZR uhC_TveUTDVl67KIwPls9L750HVIZuCKGftU.4K.DbNqCDyuyI7lkaZtlJcRs3T8u_jkB.QbHAzQ RgcIFQFbpJCkt.Q6IjUS_1ES5W1V__yqGxosVSaMziyVdMlMSzcOkiWi.1b2Bs3CY4I81HmvJpcG 9JaCz1wvMJg7mGiFFOaEz.TPyAIqMIOGndDycTPNT_miWShM3B0iygvvsP2LuuBQzo5WX0GT58Xq yhRZRTRZUTcxuGXrtLkmeuyZo5Y4KyfGbGl2kTzYJKNevKIKFBrxWcL376Q29.VRXeTokhTN8aUd ZLVHbLRTCJpo42sT3bxFEJoOmd82smm5PurYS3N7nzRaGNeY6vQ_hRLeyNACoqXpbY_fUBSG_ce8 TBA9XrDwqaic5Rke14kvf3UcyLNtlf3I3Reug7ds0pDFGb6JUrJtDH2E3ooalmPSRF37f0gHRZDe NUG9mD4Mr9IQirGCrlTP5jyQchql7wF2C89Iw8tMvjTvJb_I6WlhnEMGoIfM1hCzkkx6l8IM3W1e e5pRr9JjJRnyNOBPIzZ6tZi4ZTP2230x3NcQlWuhesXw_hS5xIN6HBpM8ncRSPIAmvytjJzEVooe mKthWEnTCcZiuKD7OPlnkvMMFRkP3GdfRIOzsUzKEH9xjDaCmRpVD6A1JsYq0.MSy2.AhjvNGQR9 yAMDFRPm7SfSaKskhbN.VaiiRIWIJ5SJkM43ynzXmGyvygKIHxU4qzKl1oSJAeIVjbK.f6hV2PzP 1z4ycCrCu8Z9BujMeoyt7fX0Pao8tTY.7RPtzM6RcGDXwP4Lwj7.6oXNr5TcwOLu3fnhi12nBwKZ r.V5_ctin_wv4yZf_WlJzCndNvuMrdGSOJif44zjUL1jkJn9mpGExvMVy_FR0B3xsa_y_TPAXMVU OIhX_ACKCX8Mvpo3BZSzvOArMe9rQrnf82a_lXUt1e45Gw0frInW0Yy1fKnd7WE3Zy1yaUgeoKc1 iuPhZ96jJn7bxj1A51JHBmu7Z64pfrTEUXDkG4zB9EfYudcLHxwb0vrzVmOCg2bSRmPnGJG96Jyy rceuwdfvpCYiSj0UpQJW8NT9a2aruVuGYCaZtSFt6Vsfhl3_KHZ2c.ZEukG1QA6Y84c0TQQV50OT 7NdVZiLfLJRlKK7bmBPbeW8EIZYACV.FqB65y1KFFvD6bXFkS2.t_0AiuMPAcZkJ4HBYLBEpdgJV Dp5ubn7oi9kWAi7yihaOSkrIiDl7BdYNg8OcSeAfz7goSPjk4nHlbIGOjdoDpSKVqqbu1hZEjrlx dEnIaP5eEppXEyelobL2VSRSWICis6mmXre22UBHcViRYeA5J9WW043cNJO3eFOZpxYlON7zk1ab 4.ig2kGgvx67shw6UTcyT5cDz0BwAj1_SgF7sYeZcF3VBfbXVDijBUHeeynbPcxTtc7XpfAZ6sMv pwf5tgl_aEkDgMUz.KdpRlx0xQzp9IHC9FJTPQDuKoonMN2sCuzPtHNTbAYg449OIbrBRgiZVQzE 9pNyA_hjzbx5D0bHv6MWCQIqU_jIYP9kZrMps3QfVrIgHqGW37_gXyt_X6kQGHG59rcN9tggKu07 IZIMoAXZiTLskjHn_f7jOcU1eBTj77tIuajC6hTMJ8rcm9.3cMkhRSvFYtj_Y1qZEr6K.RvSxMZO tIh73q6evIa_aEhFI_akCPCRn0JA07kq6gKLnH1PrQW8phn6CXy0Vpn2HW28vQpDCeYSMPNXE.ev nK2pHh6EPz00ouOV6kRKlETXFLymLDGnAbDF34CIcM1vPf8tN81kMldrd_ZNYI9wmGRbq.FduF0t MUr0GvhDYETVXe9EYVpo4FQk5j.tBO5AomJxL_EFZ2lQeOAVIjvSvMKVBfNrT.p7CjffO_OaoDGa F X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 22:59:02 +0000 Date: Fri, 21 May 2021 22:59:01 +0000 (UTC) From: Nagaraj Raj To: 'David Rowley' , Michel SALAIS Cc: 'Justin Pryzby' , 'Pgsql-performance' Message-ID: <2032281808.469685.1621637941028@mail.yahoo.com> In-Reply-To: <005201d74e84$4ee9be30$ecbd3a90$@msym.fr> 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> Subject: Re: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_469684_1657020034.1621637941025" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 16289 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_469684_1657020034.1621637941025 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi,=C2=A0 This is also not working, create table mytable_z partition of mytable for values from ('Z') to ('[')p= artition by range(id); ERROR: empty range bound specified for partition "mytable_z"DETAIL: Specifi= ed lower bound ('Z') is greater than or equal to upper bound ('[').SQL stat= e: 42P17 DB running on version PostgreSQL 11.6, compiled by Visual C++ build 1800, 6= 4-bit On Friday, May 21, 2021, 02:00:38 PM PDT, Michel SALAIS wrote: =20 =20 #yiv0089608923 #yiv0089608923 -- _filtered {} _filtered {} _filtered {} _f= iltered {} _filtered {}#yiv0089608923 #yiv0089608923 p.yiv0089608923MsoNorm= al, #yiv0089608923 li.yiv0089608923MsoNormal, #yiv0089608923 div.yiv0089608= 923MsoNormal {margin:0cm;font-size:11.0pt;font-family:sans-serif;}#yiv00896= 08923 a:link, #yiv0089608923 span.yiv0089608923MsoHyperlink {color:blue;tex= t-decoration:underline;}#yiv0089608923 span.yiv0089608923EmailStyle20 {font= -family:sans-serif;color:windowtext;}#yiv0089608923 .yiv0089608923MsoChpDef= ault {font-size:10.0pt;} _filtered {}#yiv0089608923 div.yiv0089608923WordSe= ction1 {}#yiv0089608923=20 Hi I don=E2=80=99t =C2=A0discuss here the choice itself but this is not correc= t: create table mytable_z of mytable=C2=A0 for values from ('Z') to ('Z['); =C2=A0 It should be create table mytable_z of mytable=C2=A0 for values from ('Z') to ('[') =C2=A0 Michel SALAIS =C2=A0 De=C2=A0: Nagaraj Raj =20 Envoy=C3=A9=C2=A0: vendredi 21 mai 2021 18:39 =C3=80=C2=A0: David Rowley Cc=C2=A0: Justin Pryzby ; Pgsql-performance Objet=C2=A0: Re: Partition with check constraint with "like" =C2=A0 Hi David, =C2=A0 Hi, =C2=A0 I am trying to create partitions on the table which have around 2BIL record= s and users will always look for the "name", its not possible to create a p= artition with a list, so we are trying to create a partition-based first le= tter of the name column. name column has a combination of alpha numeric val= ues. =C2=A0 =C2=A0 =C2=A0 > postgres=3D# select chr(ascii('z')+1) ; > chr > ----- > { > (1 row) =C2=A0 I tried as below, I'm able to create a partition table for 'Z', but it's no= t identifying partition table.=C2=A0 =C2=A0 =C2=A0 postgres=3D# select chr(ascii('Z')+1) ; chr ----- [ (1 row) =C2=A0 create table mytable_z of mytable=C2=A0 for values from ('Z') to ('Z['); CREATE TABLE=C2=A0 =C2=A0 insert into mytable=C2=A0 values(4,'ZAR83NB'); =C2=A0 ERROR: no partition of relation "mytable" found for row DETAIL: Partition k= ey of the failing row contains (name) =3D (ZAR83NB). SQL state: 23514 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley wrote:=20 =C2=A0 =C2=A0 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 =20 ------=_Part_469684_1657020034.1621637941025 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi, 

= This is also not working,

<= /div>

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

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


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

=20
=20
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:

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<= /p>

 

D= e : 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-performa= nce <pgsql-performance@postgresql.org>
Objet&nbs= p;: Re: Partition with check constraint with "like"

 

Hi David= ,

 

=

Hi,

 

I am trying to create partitions on t= he 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 tryin= g 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<= br clear=3D"none">> -----
> {
>= ; (1 row)

 

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

<= div>

 

 = ;

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

 =

create table mytable_z o= f mytable  for values from ('Z') t= o ('Z[');

CREATE TABLE <= /p>

 

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

 

=

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

&n= bsp;

 

=

 

 

 

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

 

<= span style=3D"font-size:10.0pt;font-family:sans-serif;color:#26282A;"> &nbs= p;

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

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

=
> same way for 9

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

https://en.w= ikipedia.org/wiki/ASCII

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

But is there a parti= cular 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 y= our use case here, so it's hard to give
any advice.

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



David

=
------=_Part_469684_1657020034.1621637941025--