CODEWITHSUNDEEP
postgresqlcase-when
PabloDK
PabloDK

Reputation: 2509

Postgresql, update or insert based by case

The update and insert statements work when i run them alone and without a transaction...

But i like to execute both in the given order in the transaction and also get the RETURNING value every time - no matter if it inserts or updates - how do i do this`?

BEGIN;
UPDATE globaldata SET valuetext=(SELECT (CAST(coalesce(valuetext, '0') AS integer) + 1) FROM globaldata WHERE keyname='bb') WHERE keyname='bb' RETURNING valuetext;
INSERT INTO globaldata (keyname, valuetext)SELECT 'bb', '1' WHERE NOT EXISTS (SELECT 1 FROM globaldata WHERE keyname='bb') RETURNING valuetext;
COMMIT;

I tried to wrap the update and insert statements by CASE WHEN THEN...but i didnt succeed...

I like to do something like:

BEGIN;
CASE
    WHEN (select count(id) from globaldata where keyname='bb') > 0 THEN
UPDATE globaldata SET valuetext=(SELECT (CAST(coalesce(valuetext, '0') AS integer) + 1) FROM globaldata WHERE keyname='bb') WHERE keyname='bb' RETURNING valuetext;
ELSE
    INSERT INTO globaldata (keyname, valuetext)SELECT 'bb', '1' WHERE NOT EXISTS (SELECT 1 FROM globaldata WHERE keyname='bb') RETURNING valuetext;
END;
COMMIT;

Upvotes: 1

Views: 3301

Answers (2)

Michaelangelo
Michaelangelo

Reputation: 21

I have been wanting to do this as well, after looking into it and a little trial and error I came up with this working solution.

use the with statement

with
u as (
  update my_table
    set some_value = $2
  where
    id = $1
  returning *
)
,
i as (
  insert into my_table (id, some_value)
    select $1, $2
  where 
    not exists(select * from u)
  returning *
)

select * from u
union
select * from i;

Try the update first returning the row updated, if there is no row returned from the update, then insert the row returning the inserted row. Then select a union of the returned values form the update and the insert, since only one will happen you will only get one row returned.

Hope this helps

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51609

try using DO

Updated 

       do 
        $$
        declare _valuetext text;
        BEGIN
        if
            (select count(id) from globaldata where keyname='bb') > 0 THEN
        UPDATE globaldata SET valuetext=(SELECT (CAST(coalesce(valuetext, '0') AS integer) + 1) FROM globaldata WHERE keyname='bb') WHERE keyname='bb' RETURNING valuetext into _valuetext
    ;
raise info '%','_valuetext is '||_valuetext;
        ELSE
            INSERT INTO globaldata (keyname, valuetext)SELECT 'bb', '1' WHERE NOT EXISTS (SELECT 1 FROM globaldata WHERE keyname='bb') 
    RETURNING valuetext  into _valuetext
    ;
raise info '%','_valuetext is '||_valuetext;
         end if;
            END;
            $$
            ;

Upvotes: 0

Related Questions