I have a table in PostgreSQL that I need to modify, the table looks like this:

 name | value1 | value2 | date         
-------- ------- -------- ---------
 Jonh |        |  0.15  | 2021-12-23 00:00:00
 Jonh |  0.14  |  0.76  | 2021-12-22 00:00:00
 Jonh |  0.19  |  0.87  | 2021-12-21 00:00:00
 Jonh |  0.13  |  0.11  | 2021-12-15 00:00:00
 Bob  |  0.12  |  0.23  | 2021-12-15 00:00:00

Having a name, a date and either value1 or value2, I need to create a statement that:

1. Checks if any row with name and date exists (there is a constraint key so there can only be one row both values)
2. Inserts the value value1 or value2 in this row (update the row values, although initially this columns will be empty in the row).

I need to set value1 and value2 at different times, so I need to find a way of creating a new row or updating the current one, I was using this answer to create a create or update row statement, how'd you do this?

EDIT

Scenario 1, as input I will have the arguments:

name='Jonh'
value1 = 0.5
date = '2021-12-23 00:00:00'

In this case, a row with this name and date already exists, so it will only update the value of value1. Final table:

 name | value1 | value2 | date         
-------- ------- -------- ---------
 Jonh |  0.5   |  0.15  | 2021-12-23 00:00:00
 Jonh |  0.14  |  0.76  | 2021-12-22 00:00:00
 Jonh |  0.19  |  0.87  | 2021-12-21 00:00:00
 Jonh |  0.13  |  0.11  | 2021-12-15 00:00:00
 Bob  |  0.12  |  0.23  | 2021-12-15 00:00:00

Scenario 2, as input I have:

name='Tim'
value2 = 1
date = '2021-12-23 00:00:00'

Because there are no rows with this name and date, it will create a new row, assign the value2 and set a NULL value for value1. Final table:

 name | value1 | value2 | date         
-------- ------- -------- ---------
 Tim  |        |  1     | 2021-12-23 00:00:00
 Jonh |        |  0.15  | 2021-12-23 00:00:00
 Jonh |  0.14  |  0.76  | 2021-12-22 00:00:00
 Jonh |  0.19  |  0.87  | 2021-12-21 00:00:00
 Jonh |  0.13  |  0.11  | 2021-12-15 00:00:00
 Bob  |  0.12  |  0.23  | 2021-12-15 00:00:00

CodePudding user response：

"Update or Insert" in SQL is commonly known as "Upsert", "Merge" or "Replace". Check the pgsql docs: https://www.postgresqltutorial.com/postgresql-upsert/ and adapt its example

INSERT INTO customers (name, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

to your needs

CodePudding user response：

You can use the INSERT ON CONFLICT syntax.

But it expects that there will be a conflict when there's a duplicate.

So for your table it needs a primary key or unique index on (name, date)

Example

create table your_table (
 name varchar(30),
 value1 decimal(10,2),
 value2 decimal(10,2),
 date 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');

-- 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

Demo on db<>fiddle here