PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update column with unique index avoiding duplicates SQL

I have 2 tables where I need to update one column with the records from the other table:

create table abc(id,b,c)as values
 ('ab1',     1,   null)
,('ab2',     2,   null)    
,('ab3',     3,   null)    
,('ab4',     4,   null)  ;
alter table abc 
   add constraint abc_c_uniq unique(c)
  ,add constraint abc_pk primary key(id);

create table def(id,e,f,fk) as values
 (1,     1,   'text1', 'ab1')
,(2,     2,   'text2', 'ab2')  
,(3,     3,   'text3', 'ab3')  
,(4,     3,   'text3', 'ab4') ;
alter table def
  add constraint def_pk primary key(id)
 ,add constraint def_fk_fk foreign key (fk) references abc(id);

I need to update the values of column c in table abc with values from f from table def. Column c has a unique constraint and there is duplicated values for column f in table def but with different foreign keys, when I try to run my query I got an error:

ERROR:  duplicate key value violates unique constraint "abc_c_uniq"
DETAIL:  Key (c)=(text3) already exists.

Here is my query:

UPDATE abc
SET c = def.f
FROM def
WHERE abc.id = def.fk;
Tags:

3

Answers


  1. 0

    You need to decide what to do in the case where there are multiple values.. here is one choice

    UPDATE abc AS abcTable
SET c = max( defTable.f )
FROM def as defTable
WHERE abcTable.id = abcTable.fk;
    Login or Signup to reply.
  2. 0

    NB: Gently reminder before you run these code make sure you begin a transaction if it is a live db

    So , I am more concerned with duplicates which Randy alluded too. How I went solving this would solely be on things I have done in the past.

    Having said that, given that there might be multiple values for f in def.
    We can gently use it to update by choosing the minimum value of fk if duplicates occurs. This will ensure that you can update abc.c with the smallest f value for each fk

    A code snippet to guide you:

    WITH f_pairs AS (
    SELECT DISTINCT ON (defTable.f) defTable.fk, defTable.f
    FROM def AS defTable
    ORDER BY defTable.f, defTable.id 
)
UPDATE abc AS abcTable
SET c = f_pairs.f
FROM f_pairs
WHERE abcTable.id = f_pairs.fk;
    Login or Signup to reply.
  3. 0

    You can use a distinct on to pick only one def.fk to be used: demo at db<>fiddle

    update abc
set c = payload.f
from (select distinct on(f)f,fk 
      from def 
      order by f,fk) as payload
where abc.id = payload.fk;

select * from abc;
    id b c
    ab4 4 null
    ab1 1 text1
    ab2 2 text2
    ab3 3 text3

    Otherwise, as the error suggests, you end up trying to use the two text3‘s from def for two different rows in abc, violating the unique constraint.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search