0

I have a problem looks like that, it would be wonderful if you can give some instructions. I am using PostgreSQL

Table:

mytable(col1,col2,col3,count)
unique constraints on col1,col2,col3

I have a insertion statement,call it

insert into myTable

It sometimes fails, because the insertion violates the unique constraints But I want to catch this error and do a update if there is unique_violation,namely

update myTable
set count=count+1
where col1=.. and col2=.. and col3=..

How can I express this semantic exactly in the language of Postgresql?

I have tried (seems to be syntactically wrong):

BEGIN;
    INSERT INTO knowledge (brand_id, brand, user_variant,score,count) 
    VALUES (%(brand_id)s, %(brand)s, %(user_variant)s,%(score)s,%(count)s);
EXCEPTION
    WHEN unique_violation THEN
    UPDATE knowledge
    SET count=count+1
    WHERE brand=%(brand)s AND user_variant=%(user_variant)s;
COMMIT;

And also python (I am using python, with psycopg2)

try:
    ...
except:
    ...

which does not work, because the try just passes the insertion error on to the exception which finally causes an error:

current transaction is aborted, commands ignored until end of transaction block

In except even the sql in except is fully correct.

I am now trying to use a pl/pgsql function, but it seems hard.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Hello lad
  • 17,344
  • 46
  • 127
  • 200
  • Rather than over complicating, why not just check if the value is in the table with a `SELECT`, if it is not then insert, if it is update. – Elias Jul 08 '14 at 16:21
  • @Elias: That approach is subject to race conditions. A reliable upsert is much harder than it looks. – mu is too short Jul 08 '14 at 16:23
  • http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql has a ton of good info on why postgres doesn't have a native upsert function. – Andreas Jul 08 '14 at 16:24
  • Since you are looking for a plpgsql function: http://stackoverflow.com/questions/17152860/upsert-with-a-transaction/17153361#17153361 – Erwin Brandstetter Jul 08 '14 at 16:25

0 Answers0