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 1l1o8r-0005bR-N5 for pgsql-admin@arkaria.postgresql.org; Tue, 19 Jan 2021 10:21:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l1o8q-0001Mx-GW for pgsql-admin@arkaria.postgresql.org; Tue, 19 Jan 2021 10:21:40 +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 1l1o8p-0001Mp-O7 for pgsql-admin@lists.postgresql.org; Tue, 19 Jan 2021 10:21:40 +0000 Received: from cloudearth.serversfarm.com ([216.244.65.90]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l1o8m-0000iX-9j for pgsql-admin@postgresql.org; Tue, 19 Jan 2021 10:21:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=ibosstech-us.com; s=x; h=Content-Type:Mime-Version:Reply-To:Message-Id:Date :Subject:To:From:Sender:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:In-Reply-To:References:List-Id:List-Help:List-Unsubscribe: List-Subscribe:List-Post:List-Owner:List-Archive; bh=V4n6CWJ//xxgMePQLUObFkjgvmpiBSFCpktwhfQjMCk=; b=cGA3nZfWif9okjRJ8GHHPRVaDu WxD4o6e3ifDflPSmE4YRIA+FLkcTa9ko0Yddaw79UzNEVbDPe6HCQXqoxrn2KkibORdskiwoIK8Pq Z/Gh5RKXQ/ZQV6yQx+S4x6c77AQHiqgNolk6so6UOoj3kBYX+fQP3Ui/5FZJIvZzVrulYEO66FFt6 Xs+2a2swYVKglj6B6Y4eTF+MEyUq1Qy/eFkR8ewbJ/j/JlyjYFB0EjqHX9DU5mG8tySrQFg2oBu2c 00Sd6kxxIAZ5FkN3mdegWr6kMNKYGCSzSVQ00WU79Vppg5TTNTxrc2DH3K5NretpkhlVTlPVD2mHy 18sBcMrg==; Received: from [171.61.169.118] (helo=[192.168.1.11]) by cloudearth.serversfarm.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94) (envelope-from ) id 1l1o8l-00H2Bm-2u for pgsql-admin@postgresql.org; Tue, 19 Jan 2021 05:21:35 -0500 From: "Anjul Tyagi" To: pgsql-admin Subject: Table Partitioning Date: Tue, 19 Jan 2021 10:21:27 +0000 Message-Id: Reply-To: "Anjul Tyagi" User-Agent: eM_Client/8.1.979.0 Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="------=_MB2744DBA7-D4A9-4019-AA63-B4B92344C28F" X-Authenticated-Id: anjul@ibosstech-us.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --------=_MB2744DBA7-D4A9-4019-AA63-B4B92344C28F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Hi All, We are using postgres 10.12 and we have one table that size is more then=20 20Gb. We are planning to do the range partition on that table, as we=20 have date filed that can be used. We are able to create partition and it=20 work fine, however one scenario where we are getting error. We have created the yearly range for the partition, however we have some=20 scenario where we have to update the date column and respective record=20 need to move from other child tables. For ex: Table X has 3 partition X2019, X2020, X2021. Column is action_date, that=20 we are using for partitioning. insert one record in X2019 and action date =3D 2019-11-16 however now i have to update the date form '2019-11-16' to '2020-02-18',=20 when we try to runt he Update statement, it give me an error called=20 "SQL-23514 - new row for relation X2019 violates partition constraint". Can you please suggest, how can we handle that scenario? Do I have to=20 write the trigger in Update statement and move the record manually or=20 perform the insert and delete operation. Regards, Anjul TYAGI =C3=BC Go Green --------=_MB2744DBA7-D4A9-4019-AA63-B4B92344C28F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi All,

We are using postgres 10.12 a= nd we have one table that size is more then 20Gb. We are planning to do the = range partition on that table, as we have date filed that can be used. We= are able to create partition and it work fine, however one scenario where w= e are getting error.=C2=A0

We have created the y= early range for the partition, however we have some scenario where we have= to update the date column and respective record need to move from other chi= ld tables.=C2=A0

For ex:

<= div>Table X has 3 partition X2019, X2020, X2021. Column is action_date, tha= t we are using for partitioning.=C2=A0

insert on= e record in X2019 and action date =3D 2019-11-16

however now i have to update the date form '2019-11-16' to '2020-02-18', w= hen we try to runt he Update statement, it give me an error called "SQL-235= 14 - new row for relation X2019 violates partition constraint".
<= br />
Can you please suggest, how can we handle that scenario? Do = I have to write the trigger in Update statement and move the record manual= ly or perform the insert and delete operation.=C2=A0

=




=C2=A0
=C2=A0
=C2=A0

Regards,

Anjul TYAGI

=C2=A0

=C3=BC<= /b>=C2=A0Go=C2= =A0Green


--------=_MB2744DBA7-D4A9-4019-AA63-B4B92344C28F--