PostgreSQL add column behaves differently to other adaptors (SQlite, MySQL) when specifying default

[lighthouse-import]

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6648
Created by John Barker - 2011-03-30 07:06:33 UTC

When adding a column to a table with a migration, like so:

  add_column :receivables, :arrears, :boolean, :default => false

and using SQLite or MySQL, the column will be added, and all rows will have a value of false for the new column. On PostgreSQL it will contain NULL.

  Receivable.first.arrears => nil

This seems a little inconsistent to me so I've written a patch, tested against 2-3-stable and PostgreSQL 9.0.3. It does the add column in one query which is consistent with the other two adaptors mentioned.

Perhaps this consistency is not required, but it's been a bit of a headache migrating from these two databases to postgresql because of it.

Of note: this way is slower, as now add_column needs to write all the default values for the table. If you need the old behaviour simply break out the add column and default:

  add_column :receivables, :arrears, :boolean
  change_column_default :receivables, :arrears, false

[lighthouse-import]

Imported from Lighthouse.
Comment by Michael Granger - 2011-04-19 14:28:33 UTC

I'm not sure why your migrations aren't doing this, but PostgreSQL does set the default values on new columns in an ALTER TABLE:

    $ psql test
    psql (9.0.3)
    Type "help" for help.

    test=> create table foo ( id serial, name text );
    NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
    CREATE TABLE
    test=> copy foo (name) from stdin;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> Tricky  
    >> Dicky
    >> Ricky
    >> Micky
    >> Maude
    >> \.
    test=> select * from foo;
     id |  name  
    ----+--------
      1 | Tricky
      2 | Dicky
      3 | Ricky
      4 | Micky
      5 | Maude
    (5 rows)

    test=> alter table foo add column signed boolean default false;
    ALTER TABLE
    test=> select * from foo;
     id |  name  | signed 
    ----+--------+--------
      1 | Tricky | f
      2 | Dicky  | f
      3 | Ricky  | f
      4 | Micky  | f
      5 | Maude  | f
    (5 rows)

[lighthouse-import]

Imported from Lighthouse.
Comment by John Barker - 2011-04-23 10:16:42 UTC

Rails actually sends separate commands to the database when you do an add_column, not a single alter table statement. Your example SQL alter table statement is the same behaviour introduced in my patch.

[lighthouse-import]

Imported from Lighthouse.
Comment by Michael Granger - 2011-04-24 03:09:22 UTC

I'm sorry, I must have read the diff backwards for some reason. Your patch modifies it to do exactly what I'd expect.

[lighthouse-import]

Imported from Lighthouse.
Comment by Juan Manuel Cuello - 2011-04-24 03:40:59 UTC

I think the patch was not created the way Rails Guides explains, so I cannot apply it for testing. Could you please re-create it?

[lighthouse-import]

Imported from Lighthouse.
Comment by John Barker - 2011-04-25 16:11:52 UTC

Just realised this was also fixed in master, so simply back-ported the fix and made the diff as per the guide, applied it with 'git am ...' and it works, passes all the rake tests.

[lighthouse-import]

Attachments saved to Gist: http://gist.github.com/971822