CODEWITHSUNDEEP
postgresql
chris
chris

Reputation: 2020

update on conflict do nothing postgres

I'm trying to update a table so that I can remove the prepended zeros from one of the columns. I want to do something like this: 

UPDATE oh_person SET identifier = TRIM(LEADING '0' FROM identifier)

The issue is that some of the rows already have the identifier without the prepended zeros, and since this column is meant to be unique, it throws an error in those instances. Is it possible to do something like this? 

UPDATE oh_person SET identifier = TRIM(LEADING '0' FROM identifier) ON CONFLICT (identifier) DO NOTHING

I know that that particular query would not be possible but is there some other syntax that would achieve that?

Upvotes: 4

Views: 4773

Answers (1)

klin
klin

Reputation: 121834

Example data:

create table oh_person(identifier text unique);
insert into oh_person 
values ('012'), ('0012'), ('0015'), ('015');

Use anonymous code block:

do $$
declare
    r record;
begin
    for r in
        select identifier
        from oh_person
        where left(identifier, 1) = '0'
    loop
    begin
        update oh_person
        set identifier = trim(leading '0' from identifier)
        where identifier = r.identifier;
    exception
        when unique_violation then
            raise notice '% not updated', r.identifier;
    end;
    end loop;
end $$;

NOTICE:  0012 not updated
NOTICE:  015 not updated

Result: 

select * from oh_person;

 identifier 
------------
 0012
 015
 12
 15
(4 rows)

Upvotes: 4

Related Questions