CODEWITHSUNDEEP
postgresql
Aijaz
Aijaz

Reputation: 730

Update with distinct on violates unique key in postgres?

update schema.current_status a
set status       = stat.overall_status,
    created_date = stat.created_date,
    parent_id    = stat.parent_id,
    id  = stat.id
from (select distinct on (id) id,  parent_id, overall_status, created_date
from schema.daily
where parent_id
          =
      'abc'
     ) stat;

This is the query I am using, when I run just the sub query, I can see it is returning unique records only, but when I try to update the table, it says it violates unique key. My unique key is on parent_id, id

Upvotes: 1

Views: 459

Answers (1)

Philippe
Philippe

Reputation: 1827

It seems the where clause is missing in query. You should have something like this

update schema.current_status a
set status       = stat.overall_status,
    created_date = stat.created_date,
    parent_id    = stat.parent_id
from 
(select distinct on (id) id,  parent_id, overall_status, created_date
from schema.daily where parent_id = 'abc') stat
where id  = stat.id;

Upvotes: 2

Related Questions