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.

possible duplicate of [POSTGRESQL INSERT if specific row name don't exists !](http://stackoverflow.com/questions/5297045/postgresql-insert-if-specific-row-name-dont-exists) — juergen d, Jun 21 '12 at 09:49

score 97 · Accepted Answer · edited Jun 06 '19 at 15:00

97

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

edited Jun 06 '19 at 15:00

Vic Seedoubleyew

9,888
6
55
76

answered Jun 21 '12 at 09:58

Dave Halter

15,556
13
76
103

1

`SELECT 3, 'C', 'Z'` How are you able to select via row value instead of column name? – insivika May 11 '20 at 16:58
@insivika that line is part of the INSERT statement, he's specifying the column name in the previous line. – Emiliano Rodriguez Nov 26 '20 at 03:02

score 15 · Answer 2 · edited Apr 13 '17 at 12:42

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.

score 7 · Answer 3 · answered Sep 11 '17 at 20:07

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)

score 7 · Answer 4 · answered May 05 '20 at 21:13

7

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();

answered May 05 '20 at 21:13

mcan

1,914
3
32
53

PostgreSQL 9.5+ only – Leonis Nov 25 '21 at 10:11

score 2 · Answer 5 · answered Dec 07 '21 at 07:34

create table urls (
      url_id serial primary key, 
      url text unique, 
      content text,
      last_analyzed timestamptz);

insert into urls(url) values('hello'),
   ('How'),('are'),
   ('you'),('doing');

By creating procedure, you also also do upsert.

CREATE OR REPLACE PROCEDURE upsert_url(_url text) LANGUAGE plpgsql
as $$
BEGIN 
     INSERT INTO URLs (url) values (_url)
      ON CONFLICT (url) 
      DO UPDATE SET last_analyzed = NOW();
END 
$$;

Test it through call the procedure.

call upsert_url('I am is ok');
call upsert_url('hello');

update if row with some unique value exists, else insert

5 Answers5

Linked