How to migrate a boolean field in MySQL to PostgreSQL in Heroku?

Question

I have a application, running with MySQL in my local machine. There is a field I define as boolean in Rails, which is represent in 0 or 1 in MySQL.

I am now trying migrate to Heroku, which is using PostgreSQL. After push the code and database to Heroku, the app cannot run.

There is an error message.

ActiveRecord::StatementInvalid (PGError: ERROR:  operator does not exist: boolean = integer

How can I fix it?

Thanks all.

dexter · Accepted Answer · 2011-08-23 16:04:06Z

3

That's because MySQL uses TinyInt(1) for storing boolean attributes where as PostgreSQL has a native boolean type.

It's better to use a migration tool like

which takes care of handling these problems.

Setup a local postgres database. Migrate from MySQL to your local Postgres DB. Then push from your local postgres DB to Heroku using taps.

Also, make sure you have not used 'y' or 'n' anywhere in the code, since these values are specific to MySQL

answered Aug 23, 2011 at 15:29

dexter

13.6k5 gold badges42 silver badges56 bronze badges

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

4 Comments

Thanks for quick response. Is there any method that I don't need to install another database in local machine?!

pls check if using an interpolated condition form (as discussed in the link) resolves your issue

MySQL has had true bit fields since 5.0.3 for MyISAM, and 5.05 for innodb and a few other engines.

Thanks dexter. I made a mistake in condition, using 1 instead of true when checking a boolean field. My Problem fixed. :)