CODEWITHSUNDEEP
sqlpostgresqlupsert
Christian
Christian

Reputation: 7419

How can I do some kind of upsert within Postgres 9.5

I have a simple table:

NAME    | TIMESTAMP | AMOUNT
--------------------------
Name1   | 123       | 1
Name2   | 123       | 15
Name3   | 124       | 3
Name4   | 125       | 1

And so on. Now I get a new record. If the name and the timestamp is equal, I want the amount to be increased by the amount of the new record, otherwise the new record should be inserted.

Something like:

INSERT INTO test.data (NAME, TIMESTAMP, AMOUNT) values ('Name1', 123, 4) 
IF not EXIST; OTHERWISE UPDATE test.data set AMOUNT + NEWAMOUNT where TIMESTAMP = oldTimestamp and NAME = oldName

Is this somehow possible with one simple sql query using Postgres 9.5?

Thanks

Upvotes: 2

Views: 165

Answers (2)

user330315
user330315

Reputation:

use the new on conflict clause:

INSERT INTO test.data (NAME, TIMESTAMP, AMOUNT) 
values ('Name1', 123, 4) 
on conflict (name, timestamp) 
  do update 
    set AMOUNT + excluded.NEWAMOUNT

This requires that you have a unique constraint (or index) defined on (name, timestamp)

If you don't want to make (name, timestamp) unique, you could use a writeable CTE:

with new_values (name, timestamp, amount) as (
  values ('Name1', 123, 4)
), changed as (
  update data 
     set amount = amount + t.amount
  from new_values nv 
   where data.name = nv.name
     and data.timestamp = nv.timestamp
  returning *
)
insert into data (name, timestamp, amount)
select name, timestamp, amount
from new_values
where not exists (select 1 from changed);

Note that (unlike the on conflict solution) this is not safe against concurrent updates and you might have race conditions

Upvotes: 3

Abelisto
Abelisto

Reputation: 15614

with t as (
  update test.data set 
    AMOUNT = AMOUNT + 4 
  where 
    NAME = 'Name1' and TIMESTAMP = 123 
  returning 
    *) -- In the t we have list of updated rows (if any)

insert into test.data(NAME, TIMESTAMP, AMOUMT)
  select 'Name1', 123, 4
  where not exists (select * from t); -- Insert new row in rows was not updated

Upvotes: 3

Related Questions