This project is archived and is in readonly mode.

#6648 new
John Barker

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

Reported by John Barker | March 30th, 2011 @ 08:06 AM

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

Comments and changes to this ticket

  • Michael Granger

    Michael Granger April 19th, 2011 @ 03:28 PM

    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 ""
        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;
        test=> select * from foo;
         id |  name  | signed 
          1 | Tricky | f
          2 | Dicky  | f
          3 | Ricky  | f
          4 | Micky  | f
          5 | Maude  | f
        (5 rows)
  • John Barker

    John Barker April 23rd, 2011 @ 11:16 AM

    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.

  • Michael Granger

    Michael Granger April 24th, 2011 @ 04:09 AM

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

  • Juan Manuel Cuello

    Juan Manuel Cuello April 24th, 2011 @ 04:40 AM

    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?

  • John Barker

    John Barker April 25th, 2011 @ 05:11 PM

    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.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>