If there is no value, update if there is an insert value

Question

I am using postgresql 9.4.1212. I am writing myBatis query, but I do not know. Can I get help?

Here is my query code:

insert into search_by_date (date,total_count) values (#{date},#{total});

The parameter is map, and if there is an value, I want to update

here my search_by_date table..

column= > no(serial primary key) , date varchar(50),total_count(int)

Possible duplicate of [Insert, on duplicate update in PostgreSQL?](https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql) — Philip Couling, Jul 16 '17 at 11:25

score 0 · Answer 1 · answered Jul 16 '17 at 11:41

0

What you are looking for in an Upsert operation. It will be a feature in PostgreSQL 9.5

You can implement it by making a function to do just the same, first check the number of rows that match. If 0 then insert if greater update

answered Jul 16 '17 at 11:41

Joey Pinto

1,735
1
18
34

score 0 · Answer 2 · answered Jul 16 '17 at 15:12

Create a Unique index on the group of columns which you need to update on conflict.

CREATE UNIQUE INDEX uidx_dt_ct ON search_by_date USING BTREE(date,total_count) ;

While Inserting use ON CONFLICT clause on the columns specified in the index.

    INSERT INTO search_by_date(date,total_count)
      VALUES( '02-05-2017'::DATE , 50 )
       ON CONFLICT(date,total_count)  
    DO UPDATE SET date = EXCLUDED.date, total_count = EXCLUDED.total_count;

If there is no value, update if there is an insert value

2 Answers2