IF UPDATE RETURNS no rows been updated INSERT data

Question

I would like to UPDATE records if they exist otherwise INSERT values into the table in one query for my Java project.

DO
$do$
BEGIN
IF EXISTS
(SELECT 1 NOTES WHERE USER_ID = '2' AND EVENT_ID ='4') THEN 
UPDATE NOTES SET NOTES='MY notes' WHERE USER_ID = '2' AND EVENT_ID ='4' 
ELSE 
INSERT INTO NOTES VALUES
((SELECT max(NOTES_ID)+1 FROM NOTES), '4, 2','1',''); END IF; END 
$do$

This is what I have until now however since Postgres 9.5 something must have changes and I get:

ERROR:  syntax error at or near "ELSE"
LINE 7: ELSE

Why not use [INSERT...ON CONFLICT UPDATE](https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql) if you're working with 9.5+? — Kayaman, May 22 '17 at 19:57
I hope you are not trying to generate unique IDs using that anti-pattern with the `max() + 1`. That will **not** work with concurrent inserts. Use a sequence if you want unique IDs — , May 23 '17 at 11:53

Michel Milezzi · Answer 1 · 2017-05-23T11:40:20.960

0

There are many ways to achieve this. You can use the new UPSERT syntax, EXISTS (just fix the semicolon as Clodoaldo mentioned) or using FOUND variable:

DO $do$
BEGIN
     UPDATE NOTES SET NOTES='MY notes' WHERE USER_ID = '2' AND EVENT_ID ='4';
     IF NOT FOUND THEN
          INSERT INTO NOTES VALUES ((SELECT max(NOTES_ID)+1 FROM NOTES), '4, 2','1','');
     END IF;
END $do$;

But beware of racing conditions.

edited May 23 '17 at 11:40

answered May 22 '17 at 20:34

Michel Milezzi

10,087
3
21
36

score 0 · Answer 2 · answered May 23 '17 at 12:00

There is no need for a PL/pgSQL block.

This can be done with a single SQL statement using writeable CTE:

with updated as (
  UPDATE NOTES 
    SET NOTES='MY notes' 
  WHERE USER_ID = 2 
    AND EVENT_ID = 4
  returning *
)
INSERT INTO NOTES (notes_id, event_id, user_id, notes)
select (SELECT max(NOTES_ID)+1 FROM NOTES), --<< Do NOT do this!!
       4,
       2, 
       'My notes'
where not exists (select * from updated);

Note that generating "unique" IDs using max(notes_id) + 1 is not safe for concurrent usage and will not scale well if the table size increase.

Also: you should use numeric literals for numbers, not strings. 2 is a number '2' is a string.

If you have a unique index on (user_id, event_id) (which you should given the nature of your question) you can also use insert on conflict which was introduced in Postgres 9.5

INSERT INTO NOTES (notes_id, event_id, user_id, notes)
VALUES (
    (SELECT max(NOTES_ID)+1 FROM NOTES), --<< Do NOT do this!
    4, 2, 'My notes')
ON CONFLICT (user_id, event_id) 
  DO UPDATE set notes = excluded.notes;

A word of warning:

If you want to generate unique IDs in a reliable, concurrency-safe and scalable way you have to use a sequence. Do NOT use the anti-pattern max() + 1

IF UPDATE RETURNS no rows been updated INSERT data

2 Answers2