Search code examples
postgresqltriggersplpgsql

update if row with some unique value exists, else insert

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.

Solution

  • 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