how to handle null in date field of postgresql

Question

I have to ignore rows which have null in date column. How can I do it?

select s."SR Closed Date"::date,
       s."Service Request Number",
       a."Activity ID" 
from sr_data_master s,
     activity_master a
where s."Service Request Number" = a."Service Request Number"
and a."Service Request Number" is not null 
and a."Service Tag" is not null 
and a."Activity ID" is not null
and s."SR Closed Date"::date is not NULL
group by s."Service Request Number",
         s."SR Closed Date"::date,
         a."Activity ID";

I am getting the error:

 invalid input syntax for type date: "NULL"

Unrelated, but: why are you using a `group by` if you are not using any aggregates? — , Sep 14 '18 at 11:23
@a_horse_with_no_name I think OP isn't familiar with `DISTINCT` hence the use of `GROUP BY` for every column. Also, look at how join is being done. — Kamil Gosciminski, Sep 14 '18 at 11:30

score 2 · Answer 1 · edited Sep 14 '18 at 11:29

The error message indicates that your "date" column (which apparently isn't actually a date column) contains the string constant 'NULL' rather than a real null value (because there is no problem casting a real null value to date value).

You could use the following condition to exclude those rows:

and s."SR Closed Date" <> 'NULL'

But the correct fix for this problem is to store date values in DATE columns. Then you don't need the casting and you can't store invalid date values to begin with.

score 1 · Accepted Answer · answered Sep 14 '18 at 11:22

null is a valid value for a date. From the error message, it seems as though you have the string 'null' stored in that column. You could either check for it explicitly:

LOWER(s."SR Closed Date") <> 'null'

Or just remove the casting to date from the group by clause.

how to handle null in date field of postgresql

2 Answers2