How to change value in array column in postgresql

Question

I have

crew_member_ids                integer[],

column in table. And want to change all entry of specific id (for example 1) to another id (for example 10)

{} - > {}
{1, 2} -> {10, 2}
{2, 3}  -> {2, 3}
{1} -> {10}

How can i do it ?

So far I managed only to select lines to be affected with

select * from initial_costs where 1 = any(crew_member_ids);

arutar · Accepted Answer · 2021-11-15 13:30:54Z

1

tables for test

create table test_tabe(crew_member_ids integer[]);
insert into test_tabe values (ARRAY[3,7,4]),(ARRAY[1,8,4]),(ARRAY[1,5],ARRAY[1,1,5]),(ARRAY[3,8,20]),(ARRAY[8,10,2]);

 crew_member_ids
-----------------
 {3,7,4}
 {1,8,4}
 {1,5}
 {1,1,5}
 {3,8,20}
 {8,10,2}

cahnge query

update test_tabe set crew_member_ids[1]=10 where array_position(crew_member_ids,1)=1;

 crew_member_ids
-----------------
 {3,7,4}
 {8,10,2}
 {3,8,20}
 {10,8,4}
 {10,5}
 {10,1,5}

edited Nov 15, 2021 at 13:30

answered Nov 15, 2021 at 13:15

arutar

1,0936 silver badges10 bronze badges

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

Comments

Ryabchenko Alexander · Accepted Answer · 2021-11-15 13:18:12Z

0

update initial_costs 
set crew_member_ids = array_replace(crew_member_ids, 1, 10) 
where 1 = any(crew_member_ids);

answered Nov 15, 2021 at 13:18

Ryabchenko Alexander

12.7k9 gold badges74 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to change value in array column in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related