Forum: Ruby on Rails Adding `:null => false` column to a table containing records

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Chris (Guest)
on 2007-01-07 14:08
I'm trying to add a `:null => false` column to an existing table that
contains existing records. I do it in the standard Rails way -
migrations:

add_column :users, :has_unread_messages, :boolean, { :default => false,
:null => false }

I get an error about the `NOT NULL` constraint being violated on table
users. Appearantly the migration tries to put NULL in the added column
for all existing rows.

One would wish `:default => false` would solve this problem. However, it
doesn't have any effect - the migration statement emits the exact same
error with or without `:default => false`.

Help?
Chris (Guest)
on 2007-01-07 14:55
In case it helps in any way, the underlying RDBMS is PostgreSQL, latest
stable release.
Chris (Guest)
on 2007-01-07 15:10
Ok, two final clarifications:

1. The error pops out when trying to perform the migration, i.e. upon
`rake migrate`

2. The exact same migration works if table users has no records.

-Chris
Chris (Guest)
on 2007-01-07 15:23
Ok, thanks to bitsweat, the problem is now clear:

In my Rails version 1.1.6, `rake migrate` tries to set `NOT NULL` and
only then to apply the default value.

In the latest Rails version, 1.2rc2, `rake migrate` applies the default
value and only then sets `NOT NULL`.

So problem is solved on latest Rails.

Thanks bitsweat.

-Chris
Hendy I. (Guest)
on 2007-01-19 17:30
(Received via mailing list)
On 1/7/07, Chris <removed_email_address@domain.invalid> wrote:
> for all existing rows.
>
> One would wish `:default => false` would solve this problem. However, it
> doesn't have any effect - the migration statement emits the exact same
> error with or without `:default => false`.

In PostgreSQL, even without Rails migrations (pure SQL or use PgAdmin
III), if you try to add a column with NOT NULL and set default to
FALSE, you still get the same error. The workaround is to:
1. add the column without NOT NULL.
2. if necessary, UPDATE all rows to FALSE
3. alter the column to go NOT NULL

--
Hendy I.
Web: http://hendy.gauldong.net
Mobile: +62 856 24889899
Yahoo Messenger: ceefour666
LinkedIn: http://www.linkedin.com/in/ceefour
Jeremy K. (Guest)
on 2007-09-26 00:44
(Received via mailing list)
On 1/11/07, Hendy I. <removed_email_address@domain.invalid> wrote:
> > users. Appearantly the migration tries to put NULL in the added column
> 2. if necessary, UPDATE all rows to FALSE
> 3. alter the column to go NOT NULL


PostgreSQL in Rails 1.1.6 used to set the not null constraint before the
default.

In Rails 1.2 and later, add_column performs these steps:
  1. alter table add column
  2. unless default is null:
    3. change column default
    4. if not null, update table set column=default
  5. if not null, alter table set not null

This should resolve the original poster's issue if using PostgreSQL.

jeremy
This topic is locked and can not be replied to.