Rails: PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint

Question

Here is the error when saving a record into postgres database on rails 3.2.12 & pg 9.3:

ActiveRecord::StatementInvalid (PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint
: INSERT INTO "sw_module_infox_module_infos" ("about_controller", "about_init", "about_log", "about_misc_def", "about_model", "about_onboard_data", "about_subaction", "about_view", "about_workflow", "active", "api_spec", "category_id", "created_at", "last_updated_by_id", "module_desp", "name", "submit_date", "submitted_by_id", "updated_at", "version", "wf_state") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21) RETURNING "id"):
  activerecord (3.2.12) lib/active_record/connection_adapters/postgresql_adapter.rb:1166:in `get_last_result'
  activerecord (3.2.12) lib/active_record/connection_adapters/postgresql_adapter.rb:1166:in `exec_cache'

The table was working fine until now (saved about 50 records before the error today). After opening the pg table in pgadmin. we found the id on the table is integer. We also found Id on other table is serial. It seems that the id should be serial so it can auto-increment. If it is, then how to convert a id column to serial from integer? If it is not, then how to fix this problem?

"serial" is just an alias for `INTEGER` with a `DEFAULT nextval('some_sequence')` ; see the user manual. — Craig Ringer, Jun 19 '14 at 05:04
Table was created with rails rake db:migrate. About 8 out of 33 tables have integer type. The rest is serial. Some tables are missing index as well. Not sure what caused the problem with postgres. The same table created by sqlite has no such problem at all. — user938363, Jun 19 '14 at 15:39

score 14 · Accepted Answer · answered Jun 19 '14 at 09:15

14

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

If your column is an integer, you cannot convert it to serial, but you can mimic, what PostgreSQL would have done, just like you created your table with a serial:

CREATE SEQUENCE tablename_colname_seq;
ALTER TABLE tablename ALTER COLUMN colname SET DEFAULT nextval('tablename_colname_seq'::regclass);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

answered Jun 19 '14 at 09:15

pozs

34,608
5
57
63

What could caused the problem? The same migrations have been used with sqlite and never have such problem. About 8 out of 33 tables has id as integer (not serial). Also some of them are missing index as well. It starts bothering us because we don't know why it happened with postgres. We thought postgres was more robust than sqlite. – user938363 Jun 19 '14 at 15:46
@user938363 again, serial is not a true type. All of your columns are `integer` (in PgAdmin, you can check it on its *Properties* > *Data Type*). The difference between your columns are just the default values & the existence of their sequences. -- There must be some difference between these table's definition in ruby, so rails decided not to build them like serials, but that should be a different question (& mainly rails / rake related) – pozs Jun 19 '14 at 15:55
@@pozs, do we need to set sequence to next available id? Or the 3 commands above have done that. – user938363 Jun 19 '14 at 16:22
@user938363 this will only fix the structures, if you'll have troubles with your values too, see http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync – pozs Jun 20 '14 at 08:16

Promise Preston · Answer 2 · 2020-08-07T09:28:33.477

I had an issue like this when working on a Rails 6 application.

I had a User model that is used to create a student and an admin:

class User < ApplicationRecord
  belongs_to :role
end

And a Role model:

class Role < ApplicationRecord
end

However, I wanted the admin and student roles to be assigned for each admin and student respectively when they are created without having a roles field in the forms, so my models were like this:

The Admin model:

class Admin < ApplicationRecord
  before_save :set_admin_role

  belongs_to :user

  private

  def set_admin_role
    # set role_id to '1' except if role_id is not empty
    user.role_id = '1' if user.role_id.nil?
  end
end

The Student model:

class Student < ApplicationRecord
  before_save :set_student_role

  belongs_to :user

  private

  def set_student_role
    # set role_id to '2' except if role_id is not empty
    user.role_id = '2' if user.role_id.nil?
  end
end

So this was throwing errors whenever I tried creating admins and students:

ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR:  null value in column "role_id" violates not-null constraint)

Here's how I solved it:

The issue was that the role_id in the users table was set to null: false in my migration file. I had to change it to null: true:

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :email
      t.string :password_digest
      t.references :role, null: true, foreign_key: true

      t.timestamps
    end
  end
end

And then also changed the User model, to have the role_id as optional:

class User < ApplicationRecord
  belongs_to :role, optional: true
end

That's all.

I hope this helps

This is perfectly documented and exactly what I was looking for! Thank you for saving me hours! — Liz, Aug 21 '20 at 13:37

score 2 · Answer 3 · answered Aug 19 '20 at 18:35

I had a very similar problem.

I'll tell what was wrong, maybe someone will find my experience useful

I had ERROR: null value in column "created_at" violates not-null constraint

and the problem was that I had a methods that ran in before_create and before_save and this methods tried to save with minor updates. So it tried to save few times.

My suggestion is to check what your code is doing in before_save and before_create

Brain · Answer 4 · 2022-04-01T10:05:00.480

0

Just add optional: true to the model which we have to map

example:

user :

class User < ApplicationRecord

  has_many :tasks
end

task:

class Task < ApplicationRecord

  belongs_to :user, optional: true

end

set ----->  null: true , in ur DB/migration/

edited Apr 01 '22 at 10:05

answered Mar 17 '22 at 08:00

Brain

1
1

Rails: PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint

4 Answers4