How to change value in array column in postgresql-CodePudding

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);

CodePudding user response：

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}

CodePudding user response：

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