Postgres query multiple where clauses not working as expected

Question

I've got the following notifications table:

| id (PK - INT) | user_id (UUID) | text (Text) | show_after (Date) default NULL |

What I'm wanting todo is select all the notifs for the user_id, I also want to ensure that we DON'T show notifs where the show_after date is in future.

So if I want to show a notification after 5 days, I'd set show_after = NOW() + 5 days.

For some reason the query I've setup is behaving weirdly and will ALWAYS return the notifications where show_after === any date, even though some of the notifs have a show_after date set in the future.

Here is the query I tried:

select * from activities where user_id = '<uuid>' OR show_after = null AND 
show_after <= NOW();

I also tried switching around the OR/ANDs without success.

Paul Maxwell · Accepted Answer · 2017-12-14 08:11:05Z

1

You need parentheses to control the logic

select * from activities 
where user_id = '<uuid>' 
and (
     show_after <= NOW()
   OR
     show_after IS NULL
    )
;

Additionally in SQL you must use IS NULL to determine if there is no value. Using the equal symbol will not work (NULL = NULL is always false).

edited Dec 14, 2017 at 8:11

answered Dec 14, 2017 at 6:56

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

2 Comments

James111 Over a year ago

Awesome! Note that I changed the date query to: show_after <= NOW() (<=) (updated this in my question )

Paul Maxwell Over a year ago

<= makes sense, changed the answer to suit. cheers. in future queries always be on the lookout for or as it can have unexpected effects with he parentheses to limit it.

Collectives™ on Stack Overflow

Postgres query multiple where clauses not working as expected

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