I have a table named customers, that has many columns: 'id', 'name', 'age', 'weight', 'hight', 'blood', 'sex', 'last name', 'datetime'.

I want to add a checking logic before inserting, which is if the datetime already exists, then update/overwrite the whole existing row.

My code:

INSERT INTO customers (id,name,age,weight,hight,blood,sex,last name,datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO 
   UPDATE SET name = EXCLUDED.name || ';' || customers.name;
   ....
   UPDATE SET name = EXCLUDED.datetime || ';' || customers.datetime;

Because the table has many columns, and I want to update/overwrite the entire row if the datetime is already exists, so is there any easier way to do this instead of UPDATE SET for each column?

CodePudding user response：

Use merge

Good examples here https://www.sqlshack.com/understanding-the-sql-merge-statement/

Your target table will be the Customers table, and you will need to create a virtual table with the record you want to insert/update, let's call it Record

MERGE Customers AS Target
USING Record AS Source
ON Source.datetime = Target.datetime And id Source.id = Target.id

-- For Inserts
WHEN NOT MATCHED BY Target THEN
    //insert the record here

-- For Updates
WHEN MATCHED THEN UPDATE SET
    //Update here

Solution not complete, you need to put your values into a virtual table and update the insert and the update statements, but you get the idea.

CodePudding user response：

You can do this...

INSERT INTO customers (id,name,age,weight,hight,blood,sex,last name,datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO 
   UPDATE SET
     name = EXCLUDED.name || ';' || customers.name,
     datetime = EXCLUDED.datetime || ';' || customers.datetime,
     ...and so on...

But it's a bad idea for a number of reasons.

1. You have to store everything as a varchar. You have no type protection and can't use any functions.
2. Searching will be complex and inefficient, as every search has to first parse the value.
3. The size of the data will continuously grow.
4. There's no indication of when the values are updated.

Assuming this is to record changes for auditing later, use an audit table to store the old values. This can be a specific table which mirrors the original column by column, but it's easier to use a single table and JSON.

create table audits (
  id bigserial primary key,
  table_name text not null,
  data jsonb not null,
  event text not null,
  changed_at timestamp default(current_timestamp)
)

Then you can have a trigger write to the audit table on every change. event can be used to record whether it was an insert, update, or delete. You can also record what user made the change.