postgres: update a row on conflict insert and return old values

Question

I need a query to update a row in table, but if the id doesn't exist it inserts default values. Also it has to avoid threads race conditions.

I found an answer here that should be fine https://stackoverflow.com/a/7927957/8372336

Using this query:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;

So I think it should return old values after updating and it should prevent threads race conditions.

But I need to add an insert if the row doesn't exist and also return the inserted values this time (old values doesn't make sense because they didn't exist).

So basically I need to do something like

INSERT INTO tbl 
    (...) VALUES (...) 
    RETURNING ..., ... 
ON CONFLICT DO
UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;

but i'm not sure if something like this could work. how can i make it work and make it sure of race conditions?

@GordonLinoff you mean to perform the insert before i run the first update snippet i posted? so in the trigger i run the insert on conflict do nothing, and then the first update snippet?? and will it ensures the race conditions? — 91DarioDev, Mar 16 '19 at 17:53

Gordon Linoff · Accepted Answer · 2019-03-16T22:16:27.727

8

Somehow, this seems to work:

insert into t (x)
    values ('a0'), ('b')
    on conflict (x) do update
        set x = excluded.x || '0'
    returning i, x, (select x from t t2 where t2.i = t.i);

I am surprised because t is in scope in the subquery, but excluded is not. Hmmm . . . perhaps that is because it is not part of the on conflict clause but part of the overall insert. That starts to make sense.

Here is a db<>fiddle for this version.

I think your code would look like:

INSERT INTO tbl (...)
    VALUES (...) 
ON CONFLICT DO
UPDATE tbl x
    SET tbl_id = 24,
        name = 'New Gal'
RETURNING (SELECT t2.tbl_id FROM tbl t2 WHERE t2.tbl_id = tbl.tbl_id) AS old_id, 
          (SELECT t2.name FROM tbl t2 WHERE t2.tbl_id = tbl.tbl_id) AS old_name, 
          x.tbl_id, x.name;

edited Mar 16 '19 at 22:16

answered Mar 16 '19 at 17:55

Gordon Linoff

1,242,037
58
646
786

thanks, but it returns old values in case of update and instead new values in case of insert? – 91DarioDev Mar 16 '19 at 18:01
1

@91DarioDev . . . In the db<>fiddle, it is returning the new value in the new value column and the old value in the old value column, for both the `insert` and `update`. – Gordon Linoff Mar 16 '19 at 18:07
sorry but i needed to return for all the columns values before of updating in case of update and currently added values in case it runned the insert. for example if row with id 24 already exists and name is 'foo' and i update name with 'bar', it returns 'foo' if the row with id 24 didn't exist i add id=24 and name='created_now' and it returns created_now – 91DarioDev Mar 16 '19 at 18:13
@91DarioDev . . . Can you either set up a db<>fiddle or describe this in terms of the fiddle I set up? From what you describe, this is exactly what the query does. The subqueries return the old value and the new values are also in the `returning` clause. – Gordon Linoff Mar 16 '19 at 18:53
It worked for me, I just wanted to check if this op was a insert or a update and then used it in the application to do what I needed. In my case I'm returning the "old updated column" and if it's null it's because it's been created now. – Felipe Menezes Jan 21 '21 at 21:10

postgres: update a row on conflict insert and return old values

1 Answers1