0

I am currently running SQLite in my development environment and PostgreSQL in the production environment on Heroku.

My current search functionality has the following method in the model entry.rb:

def self.search(search)
  where("content LIKE ? OR created_at LIKE ?", "%#{search}%", "%#{search}%") 
end

While this might work in my development environment running SQLite – Heroku running PostgreSQL is throwing up an error.

I am aware of the fact that it is bad practice to run different databases in different environments, however, for now I'll stick to it for ease of administration.

Having said that, I am wondering what the correct query would look like for PostgreSQL?

Also, could I add an if clause in my model that feeds one query or the other based on the environment I am in? Like:

if Rails.env.development?
  # SQLite query
else
  # PostgreSQL query
end

Thanks guys!

Thierry M.
  • 113
  • 1
  • 15

2 Answers2

1

You're trying to use LIKE (a string operator) with a timestamp, PostgreSQL is going to complain about that and rightly so. That will work with SQLite because SQLite doesn't actually have a timestamp type, it uses ISO-8601 formatted strings for timestamps. This is your first problem.

Your second problem is that you're trying to use a string pattern matching operator with a timestamp. This doesn't make a lot of sense to me unless you're expecting people to enter '2016-01' to find things that happened in January of 2016. If this is really what you want to do then you could cast the timestamp to a string:

where('content like :pat or cast(created_at as text) like :pat', :pat => "%#{search}%")

or use to_char to format the timestamp as desired (see the manual for the available formatting options):

where("content like :pat or to_char(created_at, 'YYYY-MM-DD HH24:MI:SS.MS') like :pat", :pat => "%#{search}%")

You might also notice that I switched from position placeholders (?) to a named placeholder (:pat) so that you don't have to repeat yourself.

Don't try to use different queries in different environments. That's just opening yourself up to a lot of headaches. Install PostgreSQL in your development environment instead so that you can develop, test, and deploy with the same database. This little problem is going to be the least of your worries if you're not using the same database in all three places. ActiveRecord doesn't give you any useful database portability so either you do it the hard way yourself or you use the same database everywhere.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Thank you for this in-depth answer, I really do appreciate it. Thanks for the timestamp to string pointer. People could potentially enter "december 17" in the search and that should bring up the entry from that date. For the migration to PostgreSQL – to be honest, I am a bit scared of this – do you have any good resources on this? – Thierry M. Jan 05 '16 at 04:19
  • You could try a different `to_char` format string (see the docs I linked to) but you might be better off handling the date separately. – mu is too short Jan 05 '16 at 04:40
  • Going to try this out – before I'll implement user authentication, I want to make the switch to PostgreSQL in development. Do you have any resources on that process? I watched the RailsCast about this but not sure how relevant this information still is. – Thierry M. Jan 05 '16 at 04:44
  • Either that or use a separate search system so that you could throw in all the various timestamp formats you want or try to parse the search string to recognize dates. – mu is too short Jan 05 '16 at 04:46
  • 1
    Also found this post that you contributed to as well. Adding this as reference for other people: http://stackoverflow.com/questions/6710654/change-from-sqlite-to-postgresql-in-a-fresh-rails-project – Thierry M. Jan 05 '16 at 04:47
  • Addition to the query you have mentioned above. I had to use " instead of ' around the query to make this work – do you know why? – Thierry M. Jan 05 '16 at 05:58
  • Which query? Which part? – mu is too short Jan 05 '16 at 06:42
  • That part 'content like :pat or cast(created_at as text) like :pat' needed to be in " " -- for some reason. :) – Thierry M. Jan 05 '16 at 08:29
-1

You can use ILIKE in this case.

def self.search(search)
  where("content ILIKE ? OR created_at ILIKE ?", "%#{search}%", "%#{search.to_datetime}%") 
end

According to postgres documentation:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

Emu
  • 5,763
  • 3
  • 31
  • 51