Rails 3 migrations: boolean (mysql vs postgreSQL)

Question

I'm trying to add a "sticky" option on my forum topics. This is how my migration looks like

  def self.up
    add_column :topics, :sticky, :boolean, :null => false, :default => false
  end

  def self.down
    remove_column :topics, :sticky
  end

This works perfect locally on mysql, but when I push the changes to heroku (which uses PostgreSQL), this is what I get when using the console

>> t.sticky
=> "f"
>> t.sticky.class
=> String
>> t.sticky = true
=> true
>> t.sticky.class
=> TrueClass

Why is the default value of this property a String?

Edit: And if I save the object, it doesn't change the sticky property, i.e. it's still "f".

I don't see that behavior on my local Postgres or Heroku's. I'm using Rails 3.0.7 and version 0.11.0 of the pg gem. BTW, I strongly recommend using Postgres locally on your dev machine. There are a number of differences between Postgres and mysql (group by, case-sensitivity, etc), and even if this isn't (or shouldn't be) one of them, eventually you'll run into something. — Rob Davis
– Rob Davis, Commented May 24, 2011 at 22:09

Denis de Bernardy · Accepted Answer · 2011-05-24 10:37:18Z

4

In psql, booleans are displayed as t or f. Depending on the DB driver, these get converted to booleans or left in their string representation.

The PDO driver in PHP does the same thing. (Or used to, anyway... I vaguely recall it no longer does in its latest version.)

answered May 24, 2011 at 10:37

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

2 Comments

Marjan Over a year ago

I see. Any idea why doesn't rails automatically convert them to boolean values?

Denis de Bernardy Over a year ago

That would be a good question/bug report for the RoR devs. :-)

Arsen7 · Accepted Answer · 2011-05-24 11:50:52Z

3

Unless you find a bug in RoR or the database driver, as suggested by Denis, you may define (override) the read accessor as:

def sticky
  ! [false, nil, 'f'].include?( self[:sticky] )
end

This will convert known 'false' values to real ruby booleans.

I recall there were at least two gems to connect to PostgreSQL databases. Maybe you can use the other one?

And are you sure that the column in the database is not defined as String? I know that in your migration it's boolean, but maybe something somewhere went wrong?

answered May 24, 2011 at 11:50

Arsen7

13k2 gold badges46 silver badges60 bronze badges

3 Comments

Sean Over a year ago

It's been a long time since I've used the ruby PostgreSQL driver, but I seem to recall TRUE and FALSE were returned as the strings 't' and 'f', respectively. Irritating as all hell since every other language's PostgreSQL binding correctly maps them to their native boolean data type. I just looked at bitbucket.org/ged/ruby-pg/src/b5361e087db3/ext/pg.c and it doesn't appear as though the state of the Ruby driver has changed any. Pg's OID for BOOLOID needs to be checked and it needs to return a Qtrue or Qfalse.

Arsen7 Over a year ago

I am using ruby-postgres (0.7.9.20080128) and my models have booleans where they should have. Maybe in my case it's because of Rails 1.2.6. Old gem + old Rails = success? ;-)

Sean Over a year ago

Could be. I'm also entirely comfortable with being wrong (and would be happy to be proven wrong!), but I didn't see a type map between PostgreSQL's bool oid and Ruby bool type in the native ruby-pg driver (I also only took a 2min glance at it). I'm guessing but am assuming that something else in Rails is doing the conversion.

Marjan · Accepted Answer · 2011-05-26 09:37:11Z

1

I'm not sure what the problem was, but I just rolled back the migration and ran it again, and it worked this time. Just putting this here in case someone else encounters a similar problem.

Thanks for your help guys.

answered May 26, 2011 at 9:37

Marjan

1,4181 gold badge15 silver badges21 bronze badges

2 Comments

Arsen7 Over a year ago

Then it could be that the column has been created with wrong type?

Marjan Over a year ago

Quite possible, even though I'm not sure why. I didn't change anything at all, just rolled back one step, and ran the migration again.

Collectives™ on Stack Overflow

Rails 3 migrations: boolean (mysql vs postgreSQL)

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related