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? — user330315
– user330315, Commented Sep 14, 2018 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
– Kamil Gosciminski, Commented Sep 14, 2018 at 11:30

Kamil Gosciminski · Accepted Answer · 2018-09-14 11:29:27Z

2

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.

edited Sep 14, 2018 at 11:29

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

answered Sep 14, 2018 at 11:22

user330315

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

Comments

Mureinik · Accepted Answer · 2018-09-14 11:22:34Z

1

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.

answered Sep 14, 2018 at 11:22

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Collectives™ on Stack Overflow

how to handle null in date field of postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related