MySQL query fails in PostgreSQL

Question

Using this query:

users = User.where('confirmed_at is NULL AND confirmation_sent_at <= DATE_SUB(NOW(), INTERVAL ? days)', 1)

In mysql goes okay but in Postgresql it fails with:

PG::SyntaxError: ERROR:  syntax error at or near "1"
    LINE 1: ...AND confirmation_sent_at <= DATE_SUB(NOW(), INTERVAL 1 day))
                                                                 ^
    : SELECT "users".* FROM "users"
      WHERE (confirmed_at is NULL AND confirmation_sent_at <= DATE_SUB(NOW(), INTERVAL 1 day))

I'm trying to understand but missing the context here. Why is the integer 1 not valid in this query?

Erwin Brandstetter · Accepted Answer · 2013-11-08 20:20:43Z

3

There is no function DATE_SUB() in PostgreSQL, so it cannot work.

This expression would work in Postgres:

... AND confirmation_sent_at <= (now() - interval '1 day')

Or, if confirmation_sent_at is a date:

... AND confirmation_sent_at <= (now()::date - 1)

edited Nov 8, 2013 at 20:20

answered Nov 8, 2013 at 20:15

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Rubytastic Over a year ago

Thanks, I totally missed that DATE_SUB() could have be a mysql function only a bit cleaner than the one below

pobrelkey · Accepted Answer · 2013-11-08 20:15:33Z

1

Try...

users = User.where(
    "confirmed_at IS NULL " +
    "AND confirmation_sent_at <= (NOW() - INTERVAL '1 DAY')"
)

answered Nov 8, 2013 at 20:15

pobrelkey

6,00123 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL query fails in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related