Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1  
( 
    id serial, 
    workdate date,
    tanknum1 integer,
    tanknum2 integer,
    tanknum3 integer);

CREATE TABLE


insert into table1(id, workdate) values (DEFAULT, '01/12/2023') returning *; 


id
workdate
tanknum1
tanknum2
tanknum3


1
2023-12-01
null
null
null



INSERT 0 1


update table1 set tanknum1 = 8 where id = 1 returning *;


id
workdate
tanknum1
tanknum2
tanknum3


1
2023-12-01
8
null
null



UPDATE 1


--first non-null is tanknum2, so it catches the value and gets updated
prepare update_first_non_null(int,date) as
update table1 set (tanknum1,tanknum2,tanknum3)=(
select arr[1],arr[2],arr[3]
from (select coalesce(arr[:array_position(arr,null)-1],arr)
             ||array[$1]
             ||arr[array_position(arr,null)+1:] as "arr"
      from (select array[tanknum1,tanknum2,tanknum3] arr) i1
)i2)
where workdate=$2
returning *;

execute update_first_non_null(9,'01/12/2023');

PREPARE




id
workdate
tanknum1
tanknum2
tanknum3


1
2023-12-01
8
9
null



UPDATE 1


--first non-null is now tanknum3, so it catches the value and gets updated
execute update_first_non_null(10,'01/12/2023');


id
workdate
tanknum1
tanknum2
tanknum3


1
2023-12-01
8
9
10



UPDATE 1


--there are no non-null left, so nothing gets caught and updated
execute update_first_non_null(11,'01/12/2023');


id
workdate
tanknum1
tanknum2
tanknum3


1
2023-12-01
8
9
10



UPDATE 1