This project is archived and is in readonly mode.
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 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 "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) -
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 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 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 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="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
John Barker
Michael Granger