Using IF statement in Postgres WHERE clause

Question

I've got a postgres table with user records in it. I want to select all records where the last_visit timestamp is within the last week OR the account was created in the last week. However, if a user hasn't visited yet, the last_visit timestamp is null (yeah, I know-- it's not set to default to the account creation date).

So I need to select all records WHERE either the last_visit is in the last week OR, if that's null, where creation_date is in the last week.

Something like this, although this doesn't work (and neither does a dozen other possible syntaxes I've tried:

SELECT * FROM users 
WHERE 
IF (users.last_active IS NULL) THEN
value(users.last_visit,timestamp(users.creation_date)) < current timestamp - 7 days
ELSE 
users.last_active < current timestamp - 7 days
END IF

Any advice?

score 3 · Accepted Answer · 2015-08-26 16:29:26Z

3

You can use coalesce() for this:

where coalesce(last_visit, creation_date) >= current_timestamp - interval '7' day

coalesce() returns the first non-null value from the provided list. So if last_visit is null, creation_date is used. Otherwise last_visit

edited Aug 26, 2015 at 16:29

answered Aug 26, 2015 at 16:25

user330315

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

2 Comments

user330315 Over a year ago

@DanBracuk: you are right. That should be "greater or equal". Thanks, corrected

Dan Bracuk Over a year ago

It was wrong in the question and other answers also.

Mureinik · Accepted Answer · 2015-08-26 16:26:38Z

0

The easiest way would probably be to coalesce those two values:

SELECT * 
FROM users 
WHERE COALESCE (last_active, creation_date) < 
      CURRENT_TIMESTAMP - INTERVAL '7 DAYS'

answered Aug 26, 2015 at 16:26

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Collectives™ on Stack Overflow

Using IF statement in Postgres WHERE clause

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related