From: Anjul Tyagi <anjul@ibosstech-us.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Table Partitioning
Date: Tue, 19 Jan 2021 10:21:27 +0000
Message-ID: <em77ea4711-7a8c-4eae-a935-4136d303ea88@iboss01108> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: anjul@ibosstech-us.com
Subject: Re: Table Partitioning
In-Reply-To: <em77ea4711-7a8c-4eae-a935-4136d303ea88@iboss01108>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox