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.
Regards,
Anjul TYAGI
ü Go Green
