On Tue, May 10, 2016 at 3:53 AM rverghese <riyav@hotmail.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.