Issue with adding a not null columns to an existing table

Question

I have problem with adding not nullable columns to a table. I read many posts about this and it should be correct.

Migration code:

  def change
    add_column :individual_trainings, :start_on, :time
    add_column :individual_trainings, :end_on, :time

    change_column_null :individual_trainings, :start_on, false
    change_column_null :individual_trainings, :end_on, false
  end

Error:

PG::NotNullViolation: ERROR:  column "start_on" contains null values...

I don't have any idea why isn't working. How should I correct write change method?

Thanks in advance.

UPDATE: It was my big fault. I was have one record in this table... I cleared table and migrate. Now it's working. Sorry for my stupidity and waste of your time.

I don't know exactly your case, but you can use this `add_column :individual_trainings, :start_on, :time, default: false ` and try if it works — ciaoben, May 10 '16 at 11:06
I recently wrote a migration to do exactly what you did. But in my case, I had deleted all of records with null values. Because from now, our database should not accept null values. Can you remove this records with null values or not? — Adriano Tadao, May 10 '16 at 11:17
have you tried something like this? `change_column :individual_trainings, :start_on, :datetime, null: false` Is very strange if you don't have any records. Are you sure about this? Could you check out via [postico](https://eggerapps.at/postico/) or psql ? Maybe you have some hidden record, I don't know. xD — Adriano Tadao, May 10 '16 at 11:33

score 18 · Accepted Answer · answered May 10 '16 at 11:17

Your migration would have worked if there would not be any record in your individual_trainings table.

Since you have records in your table,when you add the start_on column, immediately the column values gets set to null and then you impose the NOT NULL constraint on it. That's why you are getting this error.

Solution:

Just set a default value(except null) while adding the start_on or end_on columns and then go ahead with the migration to enforce NOT NULL constraint.

score 5 · Answer 2 · answered May 10 '16 at 11:07

I think it is because you are saying that :start_oncannot be null with this (explaination here):

change_column_null :individual_trainings, :start_on, false

but you are leaving it null

Add a default value when creating the column and will be fine:

add_column :individual_trainings, :start_on, :time, default: false

Amol Udage · Answer 3 · 2016-05-10T11:33:53.353

3

try this,

 def change
   change_column_default :individual_trainings, :start_on, Time.now
   change_column_default :individual_trainings, :end_on, Time.now
 end

For More details see http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/change_column_default

And To set not null contraint do this

def change
  change_column :individual_trainings, :start_on, :time, :null => false
  change_column :individual_trainings, :end_on, :time, :null => false
end

edited May 10 '16 at 11:33

answered May 10 '16 at 11:10

Amol Udage

2,917
19
27

If I try your suggestion I have this error: ActiveRecord::StatementInvalid: PG::InvalidDatetimeFormat: ERROR: invalid input syntax for type time: "f" : ALTER TABLE "individual_trainings" ALTER COLUMN "start_on" SET DEFAULT 'f' – Prezes Łukasz May 10 '16 at 11:12
Its because of `time` type, you used `time` datatype for this. So you have to set `current date` as default value. see updated code. – Amol Udage May 10 '16 at 11:15
Not exactly, because I wanted set not null for columns, not default value. – Prezes Łukasz May 10 '16 at 11:28
Yes, default value is another thing. Is very strange your error. @ŁukaszKorol let us know how you solved this problem, please. – Adriano Tadao May 10 '16 at 11:35
It was my big fault. I was have one record in this table... I cleared table and migrate. Now it's working. – Prezes Łukasz May 10 '16 at 11:42

Issue with adding a not null columns to an existing table

3 Answers3