How to find the first null value in postgres?

Question

I am new to postgres.

I have fields(title) as below:

Title      Date1        Date2        Date3        Date4        Date5   Date6

Vaccine1   01/08/1980   01/08/1981   01/08/1982   01/08/1983   Null    Null

I have a situation at work where I need to find out the first null value. In the case above, it should be Date5 based on the title. I have some other conditions which need to be checked. But this would be in my main case.

I am trying to do something as below:

case
when title ='AAA'
and
??

I am stuck right there.

Any help is appreciated. Thanks.

So you want to find a record with a certain title and data5 as null? and you want to select the first of such records? — Steve Robinson
– Steve Robinson, Commented Oct 28, 2013 at 2:24
Having multiple columns that contain the same "type" of data (such that comparisons between them make sense) usually indicates that the data model is incorrect - you ought to have multiple rows with a single column that contains the date, and then an additional column that holds the data values 1-6 that have somehow become embedded in the column names. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 28, 2013 at 7:22

mu is too short · Accepted Answer · 2013-10-28 02:58:31Z

2

If you want to know which column has the first NULL then you could do something like this:

case when date1 is null then 'date1'
     when date2 is null then 'date2'
     when date3 is null then 'date3'
     when date4 is null then 'date4'
     when date5 is null then 'date5'
     when date6 is null then 'date6'
     else null
end

Demo: http://sqlfiddle.com/#!1/6fd74/1

Note that the whens in a case are checked in the listed order and execution stops at the first matching when so this structure will tell you the first non-NULL column (assuming of course that the whens are in the right order).

edited Oct 28, 2013 at 2:58

answered Oct 28, 2013 at 2:51

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

How to find the first null value in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related