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
