From: hubert depesz lubaczewski <depesz@depesz.com>
To: Anjul Tyagi <anjul@ibosstech-us.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Table Partitioning
Date: Tue, 19 Jan 2021 12:42:46 +0100
Message-ID: <20210119114246.GA13372@depesz.com> (raw)
In-Reply-To: <em77ea4711-7a8c-4eae-a935-4136d303ea88@iboss01108>
References: <em77ea4711-7a8c-4eae-a935-4136d303ea88@iboss01108>
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
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: depesz@depesz.com, anjul@ibosstech-us.com
Subject: Re: Table Partitioning
In-Reply-To: <20210119114246.GA13372@depesz.com>
* 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