PostgreSQL Question posted in
The official documentation can be found here.

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

I have the following table:

create table table1  
( 
    id serial, 
    workdate date, 
    col1 varchar, 
    col2 varchar, 
    col3 varchar 
);

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 col1 = '12' where id = 1; /* there's no relation between col1 and any other data on this query. '12' is just a random value */

Now that I have updated it once, I want to create a query to look for the first NULL column and then update it. If col1 was NULL, then 12 would be put there. Or else, if col1 have some non-NULL value I want to update 12 in col2 (and so on…)

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)

Tags:

2

Answers


  1. 0

    Try below query that updates the first NULL column in a row with a specified value ('12'), using sequential CASE statements to check each column in order.

    UPDATE table1
SET
    col1 = CASE
        WHEN col1 IS NULL THEN '12'
        ELSE col1
    END,
    col2 = CASE
        WHEN col1 IS NOT NULL AND col2 IS NULL THEN '12'
        ELSE col2
    END,
    col3 = CASE
        WHEN col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NULL THEN '12'
        ELSE col3
    END
WHERE id = 1;
    Login or Signup to reply.
  2. 0

    It’s somewhat doable without an extensive case tree if you use arrays that offer : slices. All you do is construct an array out of the row, slice it up to where the first null is (array_position() returns the first location of a given element), concatenate your desired element there with a ||, then add the remaining slice with another ||. Demo at db<>fiddle:

    update test set (a,b,c,d,e,f,g)=(
select arr[1],arr[2],arr[3],arr[4],arr[5],arr[6],arr[7]
from (select   arr[:array_position(arr,null)-1]
             ||array[12]
             ||arr[array_position(arr,null)+1:] as arr
      from (select array[a,b,c,d,e,f,g] arr) i1
)i2)
returning *;
    a b c d e f g
    12 null null null null null null
    1 12 null null null null null
    1 2 12 null null null null
    1 2 3 12 null null null
    1 2 3 4 12 null null
    1 2 3 4 5 12 null
    1 2 3 4 5 6 12
    12 null null null null null null
    1 12 3 4 null 6 null
    1 12 3 4 5 null 7

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search