Move to PostgreSQL

July 1, 2005

Though I'm fairly comfortable with MySQL, I decided to make the move to PostgreSQL for our Ruby on Rails project. Installing it was a little hairy for someone so accustomed to Windows, but DarwinPorts greatly eased the pain. The only show stopper was the fact that I was comparing some dates in SQL using the MySQL built-in function UTC_TIMESTAMP(). All the dates in the database are in UTC format, so I needed to use that function instead of the common NOW() to do the comparison. However, this was very easily remedied with a PostgreSQL function: CREATE FUNCTION UTC_TIMESTAMP() RETURNS timestamp AS $$ SELECT current_timestamp at time zone 'utc'; $$ LANGUAGE SQL; The immediate benefit for me? My test cases run in half the time using transactional fixtures. Here's a snippet I added to turn off transactional fixtures for MySQL: Test::Unit::TestCase.use_transactional_fixtures = (ActiveRecord::Base.configurations['test']['adapter'] == 'postgresql') Now I'm able to swap back to MySQL until I'm confident about going all the way with PostgreSQL. At least Mark would have a nice GUI manager, I haven't quite found one for Mac OSX. I see that pgAdmin has binaries for Mac OSX, so I may check that out. Note: Due to some database troubles, the weblog had to be reloaded from a backup. This is a repost from a feedster cache.