Is there a function to return the first null value of a row?

Question

I have the following table:

create table table1  
( 
    id serial, 
    workdate date,
    tanknum1 integer,
    tanknum2 integer,
    tanknum3 integer,
    tank1startingvalue float,
    tank2startingvalue float,
    tank3startingvalue float,
    tank1endvalue float,
    tank2endvalue float,
    tank3endvalue float
); 

And I have inserted the following data:

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

Now I updated it, looking to the first null column of a sequence. For example: I run the following update:

update table1 set tanknum1 = 8 where id = 1;

Now that I have updated it once, I want to create a query to look for the first NULL column and then update it. For example: I already have the tanknum1 non-NULL, so when I update the query using date 01/12/2023 I want it to look for tanknum2 and tanknum3. Tanknum2 is NULL? Ok, then I'll change this value. tanknum2 is non-NULL? All right, let me check tanknum3. Is it null? (and so forth. The edge case is: if i get to tanknum3 and it's already non-NULL I don't want to update it)

I'm doing this to control a gas station inventory and I'm not able to upgrade the Postgre version. This information is arriving to me via .txt file, only with the tank number, date, starting and ending value.

How can I do that I PostgreSQL? (Using Postgre 9.6)

I tried to use COALESCE to do that. However, I get the exact reverse result that I'm looking for. Lacking ideas now (except to use a lot of CASE WHEN on my code - something that I'm trying not to do. Want to do something more elegant)

Answer 1

return the first null value of a row

Turn the row into an array and array_position(row_array,null) returns the first null value of a row. Use that with array : slicing combined with sub-SELECT-based UPDATE syntax and you can avoid the extensive case tree.

Construct an array out of the row, slice it up to where the first null is, concatenate your desired element there with a ||, then add the remaining slice with another ||. Demo at db<>fiddle:

update table1 set (tanknum1,tanknum2,tanknum3)=(
select arr[1],arr[2],arr[3]
from (select coalesce(arr[:array_position(arr,null)-1],arr)
             ||array[9]
             ||arr[array_position(arr,null)+1:] as "arr"
      from (select array[tanknum1,tanknum2,tanknum3] arr) i1
)i2)
where workdate='01/12/2023'
returning *;

id	workdate	tanknum1	tanknum2	tanknum3
1	2023-12-01	8	9	null

You can achieve a similar effect with jsonb and related functions. That's just to demonstrate it's doable, not that it's a good idea. It's best to state what your actual problem is and how you see this kind of mechanism helping you arrive at a solution.