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

Question

CREATE TABLE tlps
(
  id integer NOT NULL,
  telephone telephone[],
  CONSTRAINT tlps_pkey PRIMARY KEY (id)
)

CREATE TYPE telephone AS
   (phone_code integer,
    number character varying(9)
)

I want to update the telephone 666666666 in telephone[] to 600000000

What's the definition of the telephone type? Is it a domain over text? — pozs
– pozs, Commented Jan 27, 2016 at 14:02

klin · Accepted Answer · 2016-01-27 18:30:41Z

1

Use array_replace():

insert into tlps values
(1, array['123456789', '666666666']);

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

select * from tlps;

 id |       telephone       
----+-----------------------
  1 | {123456789,600000000}
(1 row)

Case - the column telephone is of the composite type:

create type telephone as (phone_code integer, number character varying(9));

insert into tlps values
(1, array[(1,'123456789'), (2,'666666666')]::telephone[]);

Simple update phone (2, '666666666') to phone (2, '600000000') for a given id:

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

Find the phone number '123456789' and replace it with '111222333' (we do not know phone_code nor id):

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

Btw, I don't like the idea of storing phone numbers in such a complicated structure and don't know what it was invented for.

edited Jan 27, 2016 at 18:30

answered Jan 27, 2016 at 13:56

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joaquin Raya Over a year ago

but if telephone is a type with phone_code and number?

klin Over a year ago

Please, add the type definition to the question.

Joaquin Raya Over a year ago

create type telephone as (phone_code integer, number character varying(9))

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related