Re: Inserting into a master table with partitions does not return rows affected.

On Tue, May 10, 2016 at 3:53 AM rverghese <riyav(at)hotmail(dot)com> wrote:

> I am moving towards a partitioned schema. I use a function to insert into
> the
> table. If the INSERT fails because of duplicates I do an UPDATE. This works
> fine currently on the non-partitioned table because I can use GET
> DIAGNOSTICS to get the row count on the INSERT.
>
> But when I use the Master table to insert into the partitions, GET
> DIAGNOSTICS always returns 0. So there is no way of knowing whether a row
> was inserted (I am catching the unique violation exception to do the
> UPDATE).
>

If I were you, I would put the update logic in the Trigger Function which
is used for doing the insert in specific partitions.
Of course I am not aware of the exact logic and scenario so it may not work
best for you.

>
> What is a good alternative? We are on 9.4, so the UPSERT is not yet
> available to me.
> There should be some way to know if data was inserted into the partition.
>
> Thanks
> RV
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Inserting-into-a-master-table-with-partitions-does-not-return-rows-affected-tp5902708.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com