Rails - Jan 2007 - Adding `:null => false` column to a table containing records

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?

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

In case it helps in any way, the underlying RDBMS is PostgreSQL, latest 
stable release.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 1/7/07, Chris
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> 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`.
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 Irawan
Web: http://hendy.gauldong.net
Mobile: +62 856 24889899
Yahoo Messenger: ceefour666
LinkedIn: http://www.linkedin.com/in/ceefour

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 1/11/07, Hendy Irawan
<gauldong-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> On 1/7/07, Chris
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
> > 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`.
>
> 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

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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---