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 1lkEZR-0002IR-Tv for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 23:28:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lkEZO-0007Rf-G4 for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 23:28:42 +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 1lkEZN-0007Qs-US for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 23:28:42 +0000 Received: from sonic311-25.consmr.mail.ne1.yahoo.com ([66.163.188.206]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lkEZF-0001eb-Ta for pgsql-performance@postgresql.org; Fri, 21 May 2021 23:28:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621639712; bh=c9yU6pFahNlAFgBdujAOcBP/ygJdpi09mMs+Uinbw6k=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=lsuxw2VPKLhtuXnsf3PGYZ9eh3T200dP2FRXFMOOXxboh+0mnRelivCLFiymQGoQNi5Mf6ob8b2mW+YNHhmjnCfINLllzrmcOVr6d1922lKo4ZHWtS+dOkQQDgcrHKLdOLWWWNHwR1lnObHifEiNLXy0HdRGfA/bt4fLkoP9aP/4rpYp5AqtaYd+fPtTt0hC2QT4gz9TsUIvZn1SGyhhe9G+37AzVJAB2gf3KulMjdNRr7rCH0T01O6Q5dubWwjaVep0oKrr1l6c1WhAEpT7PvOmqNUglDEjfDkCR49TCsxfu3hl77Dn069sFr40Ze3nxGZDM3IpLQaHSgSEjqyxBQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621639712; bh=7HEFFl3VYkW4LUlkWWDcYyQavFI1iSED1WyYAliuZbb=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=oaFY5SCw5bG424xHuvTago0K41+VRbR3ELkvB+Kt8XKEgXasz1xl8qBdIeEmvrsRzMmVQJj2b438mD9ccJPm6BUvTbi8TxnOYS7vhJF8NsCYkfLRk6tQMqaNbA1YYgUFTL06OF/tXjsSduXZXM/FIqRkOMeC4pvguyysuKYj2oZNr6+tLjq/7SN4raqtD7eTvZ71Lt+AdZdCwiyOng5BM+LIYkPn+/JzxjwGaRCW2ljXZUYa6y/eGlNWWOEzvJC56qmLHbK8uv5oP2J/5zXfSc6miLN3U651I05jT+p6uyyOZlnZq7d1LJ+2lTro4ZKU6rqRouPgMSPwgWyL+QXGwg== X-YMail-OSG: 5AEA3GYVM1l7CgOefS_GZQeD9CBEJoLmgIKnDZjUzc_OKY48c1c.9JaypXO9VfO 6OZazIIUhVz0pLaQ8eFST6tFGgvIIT7MUwqAL6P7M4C2IMm9_8qapWtHkpS7HXhVhsBDl9x3h5k7 LVuJNJjKG7d1ty5yrFzuHd.dhnUs1a9BcN8q0gdL0_eBb7KhDz9mgIpiIUHwpZ5fJCHx2tBxKbHf B1zcHhvWZrg_Hl0V_7qio.QxaV0icwveMa0UoUpfN1_PjWzHjX4SxXAICPp1m2MrHakWj063Lokn wTKDzoH4Ix7teb2EHiYUlZrXESPsy3oWd3l0.02yoNQzdFNMa2Jhd6jE1bdX8iaZksjvDFNcjjAj fOpxF1nTdoFHwU_MuI_jBZpGYrdTfo4SzRoko44..YnfsVSkAu9k4tbqNDsKYGqricVAoBbRnMh_ s0fCK7FJeXvt9YDPOOa3_SRDvMJlW_qz1yI0O7c0SPJKevwG9TTcGkIuB_uefl7y9SCD9hFMhd8t .msVwrDG4YIJfhCKBEo8q6RLzV7YbqscWuGLPxitH_jhKYKiCiJ6DLXVhHnZ0jPFLsAignNXUUe3 4Uu3BA8WC1Xsh2wyQzQcUsLD9LMYbCuN7zNMK2wpul78SajbgaMUiZU3vzemg1YJDA2393n8HwQ8 mYY3YyrqoMgtev4PnSEtpvAO.5ae7GRQE_tTEXAlbKmRtBiMAqE04GizRPIqwROB3cqTSHgXOYeZ Ljv.stiQAT4Y9ZeOBj0hFHW9ODIHQo2KFjgBLzV4L7StleyO1faq.JDuWJlCQC_7vceTXJgng76x AAEVcZLjKD1JZ8QTKVQo047WWSNjfhPxQrn_EMNIDdzwo7EwDJUkxjqTy2s5niyp47H._rGWtqb2 3f0Zy4Xkaa0Dq2O62dHPQEyddIRgS_RCULMpsnKnHND7gXT2rSoGTaPAnmGqq6O07v7UgYoHHH2r 4bjqBUr.IyrE3FFr9rD4h2KbAxywKsEQqHmwWQAx.PWtyeTTHmm3AF9s6M_f7k9DIwr4rDiFt7WI tByjOCJ2Idb9NEecTCtCEy28H4WPj9XHrvhu55I5Aq3KdBdyAYfXwep34EBhUnrWU4Y071PXGs0t sidNU2ldh1CtgETmIVl3tt2ZDlTWCvGvip1PnNmeYOM5lrUev7S3iL4eTl9A3eGZpiHfogtQ3qJk Cq5TTImPF9EsnLYeQ3IUNYtMqZngANUpY9hj2J29c2ILIR_tcW4Xi4JPFwZjmielq3l.hcLGU9gg .Expk20WW8z3R_Q3zwHzBMnE5vu5fcQ5WBjUyIXGPEqn01ezOr_yUpodkY6W6.F1xPXA9_v0wFon XkJcs9ISpUvEa7_eafFlA5AzU88qNCnx2G.XPMJpQCJ3kwSX4XLBkQRcmN1GY8TBmgbNO7xzjlDQ _9lzp4D2rz_9Zl5khjaFtz7T_a27YuDeGQ1OYN38qZHGfVRz6OofLLGOjNkdh68EjUE.9MxHCSHS e6iqFJSqWjvIrFtITsQ1KCJD5KkE40jvgZzdbU2I61uHxcFTwROa_ITXqAKa8yIxQVme8oCB0mPz HDXJRi_4djzRCKqrV8Lv3pHze1xx8KUV2y6I9zD0Sx1yiiAit.XmbH7gSWV5LsB6nwVwQVemSO.1 B7SnwQ71zLnXL5xIeQ.JMWAXjs617LGkZvO7eSPvWmle4aIGpID2m0zzr5UewwHZ3FBRVdruRY1H TgWhk0YuQNum_Gf.MZdgovaVWkeIg807f3uHIOoqPSRUev2.9tYVtVRqIt2ODupPOUJ4hh15MAv2 HK8gbTn.xll4EujZfffXCD9A83k0UBswcv86RaK7jbnF0mUdefmzpdu1RTWMAt6dsipQA4p4OA85 aatotd.ncTUblnpj.QMk3BEkhQnHVH4FqSEAJh6k78x66C4.EZ3Hz5KuKgSawQkQ31orcdzY5PCk TiSF8_VbOfuRtGVru8OE74rRbsbLufgUn.QBCxI7yLGgA2iWpSZZzPZkduJmh6WhJcE9CwOkpO_3 UsRjQ0AiXtUFBnDddlPS1B.iM2exWMRsVhyDESCVxb7Jq2646lcYWphNj5g5ofIgfztDt.a.KpIu F59VwpFPOr8pmJ8mIuL_i1LJgrFW09pVr2Gb46iTFY_6WQX8t1wMufTsDeMvZk_PDq_t4kC1BXXD KydkFrpftJ11OgHNuKrVsFkhKWNjSmyhL0AXk6wsifFaH0LfC4mLCVHIZHgb_P2AIRInHH4v16GY .dmSZuWg1GzM_M4Th4TWJCBOruOKD2ZmzlBfwTjqH0iaKNp9nyG0kxAHPfN6TpbYtziTebG00MTE 0ROVrmuTVPn17TD6098WX4zGYRNEOXv.udbbu4ZbVGxWyRKh8ud0PkDa8hTJRjb_5Wu6UvfT2mA7 1Pfmmkz97B95pvnnv7CNdCoH6q4PgpygjApG7fBvYbqnuuTdJJKmKV8.kEG5FY6RhikiYsnoykMC Xn4933EYLaDu2zfO4kSCSK6WUzMWM3GugCG5E1G8YQaMit6tqrv9Mo7l.xtHTNu2FX0.38Fnhz8D RVL_3l_.Z5bAVxCrZ_iwgtSwmRNrxSXhuBrrX.fkCQAxKToe19XbAhN0.g9oIAjH0mCvAbGf9Bob 8IokMrbH5GZ3vInGCSli6qllYiODFM7egKARc9gPc_iNWIVc- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 23:28:32 +0000 Date: Fri, 21 May 2021 23:28:28 +0000 (UTC) From: Nagaraj Raj To: 'David Rowley' , Michel SALAIS Cc: 'Justin Pryzby' , 'Pgsql-performance' , Michael Lewis Message-ID: <1954965228.496968.1621639708945@mail.yahoo.com> In-Reply-To: <2032281808.469685.1621637941028@mail.yahoo.com> 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> Subject: Re: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_496967_582797703.1621639708941" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 20404 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_496967_582797703.1621639708941 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable sorry, forgot to attach the test cases.Postgres 13 | db<>fiddle |=20 |=20 | |=20 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: =20 =20 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 #yiv1392522220 -- filtered {}#yiv1392522220 filtered {}#yiv1392522220 filt= ered {}#yiv1392522220 filtered {}#yiv1392522220 filtered {}#yiv1392522220 p= .yiv1392522220MsoNormal, #yiv1392522220 li.yiv1392522220MsoNormal, #yiv1392= 522220 div.yiv1392522220MsoNormal {margin:0cm;font-size:11.0pt;font-family:= sans-serif;}#yiv1392522220 a:link, #yiv1392522220 span.yiv1392522220MsoHype= rlink {color:blue;text-decoration:underline;}#yiv1392522220 span.yiv1392522= 220EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv1392522220 .y= iv1392522220MsoChpDefault {font-size:10.0pt;}#yiv1392522220 filtered {}#yiv= 1392522220 div.yiv1392522220WordSection1 {}#yiv1392522220=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_496967_582797703.1621639708941 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
sorry, forgot to attach the = test cases.




=20
=20
On Friday, May 21, 2021, 03:59:18 PM PDT, Nagaraj Raj &= lt;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);


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


DB running on version PostgreSQL 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_496967_582797703.1621639708941--