Re: Table Partitioning - Mailing list pgsql-admin

On Tue, Jan 19, 2021 at 10:21:27AM +0000, Anjul Tyagi wrote:

Hi All,

We are using postgres 10.12 and 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 we are

getting error. 

We have created the yearly 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 child tables. 

For ex:

Table X has 3 partition X2019, X2020, X2021. Column is action_date, that we are using for partitioning. 

insert one record in X2019 and action date = 2019-11-16

however now i have to update the date form '2019-11-16' to '2020-02-18', when we try to runt he Update statement, it give me an

error called "SQL-23514 - new row for relation X2019 violates partition constraint".

Can you please suggest, how can we handle that scenario? Do I have to write the trigger in Update statement and move the record

manually or perform the insert and delete operation. 

 

The simplest solution would be to delete the rows, and immediately

insert into appropriate partition.

 

for example:

 

with q as (delete * from x where action = '2019-11-16 returning *)

insert into x select other, columns, '2020-02-18' from q;

 

depesz