postgres : Update rows but ignore those which on update violate a unique constraint

Question

I am updating a table which has 4 columns together as primary key.

|Col1|Col2|Col3|Col4|val|
|   1|   2|   3|   4|234|

Col 1-4 make primary key

I need to update Col1 in some rows. But some rows already have the value which i want to update. Thus when i run the query it gives :

ERROR:  duplicate key value violates unique constraint "datavalue_pkey"
DETAIL:  Key (Col1, Col2, Col3, Col4)=(609, 76911, 164, 1) already exists.

How can i ignore the cases which are already present so that update query runs fully??

Update Query :

update datavalue dv set Col1 = 6009
where concat( dv.Col1 ,'-',dv.Col2,'-',Col3,'-',dv.Col4) 
in (
     Select concatenated id ... from same table
   )

Thanks

POSTGRES - 9.5.12

Answer 1

Depending on your application, I would suggest to write a function, but I guess what you're looking for is something like this (quick & dirty):

Test table and dataset:

CREATE TEMP TABLE t (id INT UNIQUE, des TEXT);
INSERT INTO t VALUES (1,'foo'),(2,'bar');

Update ignoring conflicts.

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT * FROM t  LOOP 
    BEGIN          

      UPDATE t SET id = 2 WHERE des = 'foo' AND id = r.id;
      UPDATE t SET des = 'bar2' WHERE id = 2 AND id = r.id;  

      EXCEPTION WHEN unique_violation THEN
        RAISE NOTICE 'Oups .. there was a conflict on % - %',r.id,r.des;
    END;    
    END LOOP;

END$$;