CODEWITHSUNDEEP
postgresqlruby-on-rails-4rails-activerecordrails-postgresqlpostgresql-9.3
Fellow Stranger
Fellow Stranger

Reputation: 34013

Find records where a timestamp is present

I'm migrating from SQLite to PostgreSQL, and the following query is not working anymore:

where("my_timestamp is NOT NULL and my_timestamp != ''")

How can I find all records that have a certain (datetime) attribute present?

Upvotes: 0

Views: 149

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Assuming that your my_timestamp column is a real timestamp (i.e. t.datetime in ActiveRecord parlance) then a simple NOT NULL test is sufficient:

where('my_timstamp is not null')

If this is the case then your query should be giving you an error like:

invalid input syntax for type timestamp: ""

pointing at your my_timestamp != '' test. Your comparison with an empty string worked fine in SQLite because SQLite doesn't have a real timestamp type, it just uses ISO 8601 formatted strings in text columns; this data type problem is also why you ended up with '' in your timestamp columns in SQLite in the first place.

Upvotes: 3

Related Questions