3

i need your help,

I have a query with n times loop operation of insert and update.

I decided to use 'UPSERT', since it doesn’t require loop operation.

ex:

WITH upsert AS 
(UPDATE employee_table 
SET rollno=input_rollno,
name=input_name 
RETURNING *)
INSERT INTO  employee_table (rollno, name) 
SELECT 'input_rollno',input_name 
from employee_table 
WHERE NOT EXISTS (SELECT * FROM upsert);

The query is working fine and now the problem is, i want the affected row counts of individual insert and update..

i tried adding 'GET DIAGNOSTICS' after update but the query fails. i can take row count only after ending upsert.

When i added get diagnostics rowcount after update i got error: ex:

-- THIS IS WRONG. It's an EXAMPLE.

declre
num_rows INTEGER :=0;
num_rows1 INTEGER :=0;

WITH upsert AS 
(UPDATE employee_table 
SET rollno=input_rollno,
name=input_name 
RETURNING *)
GET DIAGNOSTICS num_rows = ROW_COUNT

INSERT INTO  employee_table (rollno, name) 
SELECT 'input_rollno',input_name 
from employee_table 
WHERE NOT EXISTS (SELECT * FROM upsert);
GET DIAGNOSTICS num_rows1 = ROW_COUNT;

i need to know how many rows got effected while updating and how many while inserting...

Using PostgreSQL 9.3

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Sharath Tanish
  • 51
  • 3
  • Please decide on one version of database. You can't have both MySQL and 3 diffrent postgres versions. – Jakub Kania Sep 29 '15 at 17:41
  • Also, your query first query updates all rows in the table or inserts a new one if the table is empty, are you sure that's what you wanted? – Jakub Kania Sep 29 '15 at 17:44
  • 1
    That approach to implementing an upsert will not work correctly unless you take an `EXCLUSIVE` lock on the table. See http://stackoverflow.com/q/17267417/398670 . You really need `insert .. on conflict update ..` from PostgreSQL 9.5, or to use a loop. – Craig Ringer Sep 30 '15 at 03:35
  • @JakubKania: Sorry. its postgresql-9.3 – Sharath Tanish Sep 30 '15 at 04:53
  • @JakubKania : scenario is, if there is an entry already in the table it need be updated, and if its empty a new row need to insert. now i want to know the count of total rows inserted and updated. depending on the count i have other functions to execute. – Sharath Tanish Sep 30 '15 at 04:57
  • @CraigRinger Since I’m using postgresql-9.3 the Procedure also have additional query to execute. the EXCLUSIVE lock on table requires more time than the usual. Presently Upsert is working very fine. but i only i need to know is how many columns updated and how many inserted. – Sharath Tanish Sep 30 '15 at 05:10
  • @SharathTanish OK, just so long as you know it's not reliable and will fail as concurrency and load increases, resulting in duplicate key errors despite the attempt at an upsert. To get an affected row count won't be easy either; maybe someone else has ideas about that. – Craig Ringer Sep 30 '15 at 05:33
  • UPSERT can be inefficient by nature depending on indexing. I prefer to do a DELETE on match, count those rows, then INSERT everything. Once you know that, you can determine how many rows were updated, and how many inserted using simple math. – Nick Pfitzner Sep 30 '15 at 12:56

0 Answers0