I have a rails 2.3.5 app that I started with MySQL 5.0 as its database and that I later switched to PostgreSQL 8.3. There were a few bumps making the switch that I’d like to share.

PostgreSQL is More Strict Than MySQL When Working with Fixtures

The first thing I noticed was PostgreSQL was more strict about the data may app loaded via fixtures. The app includes several yaml and csv fixtures providing a default data set as part of its migrations. MySQL accepted all the fixtures just fine when I ran migrations but PostgreSQL complained about errors in the csv data. It turned out there were a few stray commas and quotation marks in the csv that was throwing off the columns. MySQL apparently ignored the formatting error and skipped what amounted to extra columns on some rows but PostgreSQL demanded that everything be perfect.

Ordering Results

After switching to PostgreSQL I noticed some funny things happening to my data. For instance, I could get a list of results from a call to find, edit one of the returned results then make the call to find a second time and the record I edited would be missing. At first I thought I was some how deleting records by editing them but thankfully that was not the case.

MySQL, SQLite, MS SQL, Oracle, all of these automatically order results off a table’s primary key unless you specify something else to order by. PostgreSQL, on the other hand, does not automatically order by primary key. If you do not specify a column to order by the records you get back will seem somewhat arbitrary. In my case, I was always getting the same results until I edited a record, then the edited record would no longer show up. Once I added an order argument to my find calls the problem went away.

ILIKE and LIKE in Find Conditions

After switching to PostgreSQL I noticed my searches were suddenly case sensitive. I had a simple search call that was a model find passing a condition where the model name was like a search term.

model.find(:all, :conditions=>["name LIKE ?", params[:search]], :order=>:id)

PostgreSQL interprets the LIKE operator as indicating a case sensitive comparison. To perform a case insensitive comparison PostgrSQL uses the ILIKE operator.

model.find(:all, :conditions=>["name ILIKE ?", params[:search]], :order=>:id)

My actual implementation performs a look up of the database adapter and uses the ILIKE operator only if the current database is PostgreSQL.

Weird Issue with Dynamic Associations

My app uses the acts_as_taggable_on gem to let users tag content. The gem allows models to be tagged via dynamic associations, a very handy feature. The call I make to tag content is:

@tagger.tag(@taggable, :with => "tag1, tag2", :on => :dimension)

This worked fine until I switched the app’s database to PostgreSQL. With PostgreSQL as the database, tags made via dynamic association like the example above stopped automatically saving. I haven’t found the cause of the problem, but I did find a work around by calling the touch and save methods on my taggable models after setting a dynamic tag like so:

@tagger.tag(@taggable, :with => "tag1, tag2", :on => :dimension)
@taggable.touch
@taggable.save

um… yah.

So far these are the only issues I’ve had with PostgreSQL. Anyone have one of their own they’d like to share? if so leave a comment.

•