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

User · Answer

Firstly It tries insert  If there is a conflict on url column then it updates content and last analyzed fields  If updates are rare this might be better option   INSERT INTO URLs  url  content  last analyzed  VALUES                 url s            content s          NOW          ON CONFLICT  url   DO UPDATE SET content   content s  last analyzed   NOW

User · Answer

I found this post more relevant in this scenario   WITH upsert AS        UPDATE spider count SET tally tally 1       WHERE date  today  AND spider  Googlebot        RETURNING     INSERT INTO spider count  spider  tally   SELECT  Googlebot   1  WHERE NOT EXISTS  SELECT   FROM upsert

User · Answer

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

User · Answer

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

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

Examples related to postgresql

Examples related to triggers

Examples related to plpgsql