Postgresql, update if row with some unique value exists, else insert

Question

I have a URLs table. They contain

(id int primary key, url character varying unique, content character varying, last analyzed date).

I want to create trigger or something(rule may be), so each time i make insert from my java program, it updates some single row if row with such URL exists. Else it should perform an Insert.

Please, can you provide a complete code in Postgresql. Thanks.

Answer 1

This has been asked many times. A possible solution can be found here: https://stackoverflow.com/a/6527838/552671

This solution requires both an UPDATE and INSERT.

UPDATE table SET field='C', field2='Z' WHERE id=3; INSERT INTO table (id, field, field2)        SELECT 3, 'C', 'Z'        WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3); 

With Postgres 9.1 it is possible to do it with one query: https://stackoverflow.com/a/1109198/2873507

Answer 2

If INSERTS are rare, I would avoid doing a NOT EXISTS (...) since it emits a SELECT on all updates. Instead, take a look at wildpeaks answer: https://dba.stackexchange.com/questions/5815/how-can-i-insert-if-key-not-exist-with-postgresql

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$      DECLARE      BEGIN          UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;          IF NOT FOUND THEN          INSERT INTO tableName values (value, arg1, arg2);          END IF;      END;      $$ LANGUAGE 'plpgsql';  

This way Postgres will initially try to do a UPDATE. If no rows was affected, it will fall back to emitting an INSERT.