Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table your_table (
 name varchar(30),
 value1 decimal(10,2),
 value2 decimal(10,2),
 date date
-- , primary key (name, date)
)
insert into your_table (name, value1, value2, date) values
('Jonh Deo', null, 0.15, '2021-12-23'), 
('Jonh Deo', 0.14, 0.76, '2021-12-22'), 
('Jonh Deo', 0.19, 0.87, '2021-12-21'), 
('Jonh Deo', 0.13, 0.11, '2021-12-15'), 
('Bob Savage',  0.12, 0.23, '2021-12-15');
5 rows affected

-- trying to insert a conflicting duplicate
INSERT INTO your_table (name, value1, date)
VALUES 
('Jonh Deo', 0.5, '2021-12-23')
ON CONFLICT (name, date) 
DO UPDATE SET value1 = EXCLUDED.value1;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification


-- maybe it needs a PK or unique index? 
ALTER TABLE your_table
ADD primary key (name, date);
-- trying to insert a conflicting duplicate again
INSERT INTO your_table (name, value1, date)
VALUES 
('Jonh Deo', 0.5, '2021-12-23')
ON CONFLICT (name, date) 
DO UPDATE SET value1 = EXCLUDED.value1;
1 rows affected

-- now with completely original records
INSERT INTO your_table (name, value1, value2, date)
VALUES 
('Tim Bok Toh', null, 1.0, '2021-12-23'), 
('Tim Bok Toh',  0.6, 1.2, '2021-12-24')
ON CONFLICT (name, date) 
DO UPDATE SET value1 = EXCLUDED.value1
            , value2 = EXCLUDED.value2;
2 rows affected

select * from your_table


name
value1
value2
date


Jonh Deo
0.14
0.76
2021-12-22

Jonh Deo
0.19
0.87
2021-12-21

Jonh Deo
0.13
0.11
2021-12-15

Bob Savage
0.12
0.23
2021-12-15

Jonh Deo
0.50
0.15
2021-12-23

Tim Bok Toh
null
1.00
2021-12-23

Tim Bok Toh
0.60
1.20
2021-12-24

name	value1	value2	date
Jonh Deo	0.14	0.76	2021-12-22
Jonh Deo	0.19	0.87	2021-12-21
Jonh Deo	0.13	0.11	2021-12-15
Bob Savage	0.12	0.23	2021-12-15
Jonh Deo	0.50	0.15	2021-12-23
Tim Bok Toh	null	1.00	2021-12-23
Tim Bok Toh	0.60	1.20	2021-12-24