how to update an element in an array searched by 'where' in 'postgresql'? -

create table tlps (   id integer not null,   telephone telephone[],   constraint tlps_pkey primary key (id) )  create type telephone    (phone_code integer,     number character varying(9) ) 

i want update telephone 666666666 in telephone[] 600000000

use array_replace():

insert tlps values (1, array['123456789', '666666666']);  update tlps  set telephone = array_replace(telephone, '666666666', '600000000') id = 1;  select * tlps;   id |       telephone        ----+-----------------------   1 | {123456789,600000000} (1 row) 

---

case - column telephone of composite type:

create type telephone (phone_code integer, number character varying(9));  insert tlps values (1, array[(1,'123456789'), (2,'666666666')]::telephone[]); 

simple update phone (2, '666666666') phone (2, '600000000') given id:

update tlps  set telephone = array_replace(telephone, (2,'666666666')::telephone, (2,'600000000')::telephone) id = 1; 

find phone number '123456789' , replace '111222333' (we not know phone_code nor id):

with sel (     select id, u.phone_code     tlps, unnest(telephone) u     u.number = '123456789'     ) update tlps t set telephone = array_replace(telephone, (phone_code,'123456789')::telephone, (phone_code,'111222333')::telephone) sel s t.id = s.id; 

btw, don't idea of storing phone numbers in such complicated structure , don't know invented for.