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 1lk8Ad-000474-Pa for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 16:38:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lk8Ab-0005SZ-6X for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 16:38:41 +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 1lk8Aa-0005SR-SH for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 16:38:40 +0000 Received: from sonic315-20.consmr.mail.ne1.yahoo.com ([66.163.190.146]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lk8AX-0004C3-LB for pgsql-performance@postgresql.org; Fri, 21 May 2021 16:38:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621615114; bh=hqUVTFpB14EPDEUa5bUNc8cJvNog0Kujintp/oRY2Pc=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=poos/OTVS95TzRhousMshsf/FmLCiajK8yCcsVkmXbwc153NOOBz7aP3YeDdnjscZeUTRtyMA/hMbTOWm/mYLOmSX5kj1dVXAbGELneKKBlSM1IqlppmoUq5CCjJi683WesEHMUf0pSeTJZjqDaadA/pgiM88Kpw25YKQn3nEh7ZAunYt1IzrVhfxz1/a++0I1W/1PVRtSBdBT0oWqKVQpB94s2J2LeD4A2Al383J29BFrUXvLNZCNojUSaluSPFVmDSlnqMag1PlvDYrH1iZq/f9JYVmUkHfQYeaISf6WX7L3ukZhfpo1mU2S94Kwoi4XBecyqUJBmQj0FHPkCO2w== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621615114; bh=JQ5amrjOAI2/hQef0Us0+DYcFG2qUrjLjcM/wVOFLLW=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=rKTowFQW87YyTC8bCr2hAvr/75CSulzBAseHUiUyAt+Xflp64KZ2HnpdQ4TGx2DJLNx94HJ+X4a25onolD8M1otCNnG1cYcu59Hgu3WcJnOnYjplNxjWe9rmLnGnTOe23OSzhje8mvKx3mHuFatLXUD4QR1iUIC6ae6dwWtgwPwRCs5eG9ldswLNmQI89a8oThSRlwCf9Po+nxTe1LfUGELExdk93vJXLz0BUOmsVC3RiP6dPf2UV7gvqSKPzfY/hCmBZp7cH2JkcCjnlSCAyqv8gLsMsHlvxY2FdQwRtSEALLzsW5rb5wF4aAWz4OUMLk9l2Iw3IQHMYrmS+bVDxA== X-YMail-OSG: fqVPfP4VM1lU1A5uMejdaPBI_hl6OScr9n11F9UDdf9m4gysfUeJsRAGeQcdRpS sNymqljoAMeBRiJBtAQTT29sU6NXoKKBsbA5TkxpsWh5zSj3zcvP0tyEz5PDY2sWrdP4MKMF6Xri NgaIYlNt1lnAR1O.MtadsfllDq_eot0mqDom.zahbenHWFx7N5.wNB0OlArQ9gFs96cAqqqF7jI. cWrb3Qv3HFEA1QNHwKT3VjoWM_R.S6iv0uqQEi50VDtnnIONfbuvZ2eSiYi6o0quHQa3Lts2GB44 r..ELqlqpjwMzJwQc3RFWzpcgpxJZ8SWoQckppkc1Uai2WA62d6UEhwWVDNSOPZW.U_iiQYvT.Ry p4mS0fuBwND6nGoCvvu3r3iwx9WBBL7Q0AvnXpc0jWg40ehptQ3Shk56qwJMfD.wChLoATBY5UcE PLKL2j9pzCxSg1R.Tpx7rwApgKvPyly6bJTjnsrovkusaGy.twqPIMLjkwvy8G7JxZ1rM_kz2KPx 4apKUi1pnkIic96YI70ss3pUaEuEhY_YFq8xcBar907bJyXgpJ75SWVWh560ZNS7.5XF9h_8HdW. YSbRcU9.pVNLPLkG75efp2u5fqxQzoQCdNlMe.tBXOh0FJXEvVjvT3B73478WkI_g4HNc8UdsdQ5 5_d5gjzpvy0Ry9Oq7NFzx7UJCq2WtWt66Yrp1JvJv0mhEx0N_vrELzYJQK1hb12gi1JLbWNdq_ln DMRbNtb3YdAFWfjX1QBXMYncnTFoygnvnx5BQ40EaqZrhtpp0syVtTTDAjg5k9Wcj2uA_BFKiJ0Q 7oumqzn.tcRoUjtl0BXYronqeE3Ai2j4E3BHQ.y_0TwP.WwfpG2Yo2ST4FBlOrZKM97_cXRyyerQ sVr.PltmV1f3YcAFkOYYIlIK7fzJO31AL9p4Tl_fbUn_EqwC5MhckaChldgSupw362ui32jWsof7 JemMfHbr7.PeNZWQAZZxIznMHN_OsyCqU5JMWerNOyUjkYCqqclC7At_Sf8fyF7zGsJ1aCEDwFnq n8ss8pp0zakG4yNIq2E80OAVewiUxSDJBSCoNyTPO_5OrcqGkn1XtuwP2QKtNt4.p_agE2v_3zcN Bm2wzo1bvjczYD272PGVntWZjn4hRNJ9NJp8mu_1XH.3XTc0jPxPhqxq.nZCWAaEZIuWmUUz2eYu U_7xRVJw9WoZ3buBY.zRR7NfI2WlPMAWRVZEWghu6psbmQA2E1bzoUoGhMtkuEtndvV6lbYoKxM2 6gOt3cNPYujHA7uDRoDcPBsWXdWBuC7hpp5sjSSgvKD3MmlSkOIEaHpT0pRsoa94fCBrGF9CRlXp _a.clJQfTWgmyv2ZSLAAbBYSaaZSyXyX6zOZMBiZpLxkbGotgYii.1lm9MOVmgHCM_XFN2GChqlj TQWPC7ma7RrecGKW6u3MO4YFYRbldr_8IFjqIBqkLYAYqaQ6DjmU4fpZZkbnRjODSneihsmmqOLf 5.bqFMP3BEanfJVZT9LQUAUcwtmO6zcj.wyhDzwlWsa2xxykNaEAdVULv8dV37DuxO8welQ_2wmH cklH5mspjScYtzX61MH85huawLk6xFfOT9A8943QRfgqIWtVdbxpNKna4mJnl6S9CGDj4IulnrvR zbnUMzhbYWb36yu_UTRrpAGZqs9syWGQI6IyTZLSVWrRghSCUS9Vh8zLBVOsh0fGqQ7J9OTK4GRt xckecVS0KGOmZbIeN_uGJjU5hqDmk5OjVl5.pAqTLZXeFPm.nfNyWxZtn5GlvW12kS44U4hZb2Wj mXBLR8iIJnztykxpUBsbeDRFsq3_jEGmy4szqjIQWmiMaC8fQ3iceyd5P5LOEfd.fzLIg5f6vP7f 4hqMptoFyhVhm3GFv5SD0ffkXBR2E1Byp7_w2h8NuvfXH0P72.RkA_mukLHyjRbhkXG0c9wcRz_9 aJ1CcNyR7gp_jvhmyygbAQsGtKNpK6W6P0y9JUMT4lyry_WqQfMIGUWbBWgorLu7aZLwxArLQuMR GjZkVB_NidY5gEAixWfkwf0wZ4SBWT6NTKkn.OlAcMG2Ow2ejhMa5wCxHOJqyKJptFxWds3uxFhh p53BKxgAdcRiPuDRD_kDjSVYctHwS1Uq7Z_efupzvYzeJm8wY0lyqQklKh2.agGwCOcmkICybokc rCzQCFIsR7cEhlVs_lEg3uI_flFVpLTgAh8GJVEWzQ5LpuGUp696ZUlL2PESakxnO24T0PeaBu2N vlDdGhftXgBIMgJ22PVtgMLuPEMftiMWO7nmwi8FwEY3aoDOQVH1tDNf02hLlamj6hGxjvmW2kPn qXOZVYVU7URaiUXG9mtDjs2FDf2rpn6w7tay9rmwAtZQfIOmCVJMraLkoCGvrYdt8IXKerIgupX9 kBu2ukx689DLOOY7EX1vAaaW_1mDBaWDvwPQBpMekLBQJffz_dhWGeexiV5F2LaHKw0hCk3s.XNe xUmZv8XUkLaABHibuY2jD37Gdios8OmcLM4yhZRDn8Ou6BANi9wCanQ.iwQiWD4gRzAHS.1ZW_u1 Gdm3oeKsSq4nVi5b7Q26KhMtHUOm32zUbO56dIFOWOPbu6t978kM48bKT6m7f91xZBrXUU7s4KXA ktKwLqcHoDBFtpWy4J0AYtQPGAUYmHCxpbNhqIcU6piAS X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic315.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 16:38:34 +0000 Date: Fri, 21 May 2021 16:38:30 +0000 (UTC) From: Nagaraj Raj To: David Rowley Cc: Justin Pryzby , Pgsql-performance Message-ID: <2126516115.347941.1621615110621@mail.yahoo.com> In-Reply-To: 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> Subject: Re: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_347940_1874406433.1621615110619" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 9992 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_347940_1874406433.1621615110619 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi David, Hi, 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. > 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 no= t identifying partition table.=C2=A0 postgres=3D# select chr(ascii('Z')+1) ; chr ----- [ (1 row) create table mytable_z of mytable=C2=A0 for values from ('Z') to ('Z[');CR= EATE TABLE=C2=A0 insert into mytable=C2=A0 values(4,'ZAR83NB'); ERROR: no partition of relation "mytable" found for rowDETAIL: Partition ke= y of the failing row contains (name) =3D (ZAR83NB).SQL state: 23514 On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley wrote: =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 =20 ------=_Part_347940_1874406433.1621615110619 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi David,

=
Hi,

<= /div>
I am trying to creat= e partitions on the table which have around 2BIL records and users will alw= ays 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 col= umn. 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 parti= tion table. 


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

create table mytable_z of mytable  for values from ('Z') to ('Z[');<= /div>
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





=20
=20
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
> FRO= M (Z) to (?) ;

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

> same way for 9

p= ostgres=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 partitio= n this way? It
seems like it might be a bit painful to ma= intain, especially if you're
not limiting yourself to ASC= II 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 g= ive
any advice.

Ther= e are more details about partitioning in
https://www.postgresql.org/docs/current/ddl-partitioning.ht= ml

David


------=_Part_347940_1874406433.1621615110619--