From: | "Anjul Tyagi" <anjul(at)ibosstech-us(dot)com> |
---|---|
To: | depesz(at)depesz(dot)com |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Table Partitioning |
Date: | 2021-01-19 12:01:12 |
Message-ID: | em953b223e-5ce1-4b39-a943-f101af602baa@iboss01108 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Can we handle that in DB side with trigger?
Regards,
Anjul TYAGI
ü Go Green
------ Original Message ------
From: "hubert depesz lubaczewski" <depesz(at)depesz(dot)com>
To: "Anjul Tyagi" <anjul(at)ibosstech-us(dot)com>
Cc: "pgsql-admin" <pgsql-admin(at)postgresql(dot)org>
Sent: 1/19/2021 5:12:46 PM
Subject: Re: Table Partitioning
>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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2021-01-19 12:12:04 | Re: Table Partitioning |
Previous Message | hubert depesz lubaczewski | 2021-01-19 11:42:46 | Re: Table Partitioning |