Compare multiple column with single value in postgresql

Question

Below is my table structure

ID    DATE1           DATE2         DATE3
1     2018-05-01      2018-05-01    2018-11-11
2     2018-05-01      2018-10-01    2018-05-01
3     2018-05-01      2018-05-01    2018-05-01

Here actually I wanted to select record where any one of the three dates is greater than now()

like - (DATE1, DATE2, DATE3) > NOW()::DATE

Can anyone let me know that how can I achieve this in where clause instead of writing like

SELECT *
FROM table1
where DATE1 > NOW()::DATE
  AND DATE2 > NOW()::DATE
  AND DATE3 > NOW::DATE

Actually, I am going to compare all dates column with the single value, so just wanted to know is there any other efficient way that I can use instead of adding the condition for each column.

I assume the NOW value will only be evaluated once for each query anyway. — jarlh
– jarlh, Commented Sep 7, 2018 at 12:36
You say "where any one of the three dates is greater than now()", but then you test all dates > now(). Do you mean any or all? — jarlh
– jarlh, Commented Sep 7, 2018 at 12:39

Gordon Linoff · Accepted Answer · 2018-09-07 12:33:23Z

2

I would use GREATEST():

SELECT t1.*
FROM table1 t1
WHERE GREATEST(date1, date2, date) > CURRENT_DATE;

I think CURRENT_DATE makes more sense than converting NOW().

answered Sep 7, 2018 at 12:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Tushar Kulkarni Over a year ago

@Gordan, Thanks for the answer. Just let me know one more thing, that how we can compare it with NULL.

jarlh Over a year ago

@TusharKulkarni, Start with adding a few null values to the sample table data, and adjust the expected result accordningly.

Collectives™ on Stack Overflow

Compare multiple column with single value in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related