Postgresql error on inserting new row 'PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint'

Question

I am just migrate my us server to an eu region on heroku, and restore the old database backup to new server. Unfortunately I am getting following error while try to insert an new row to one of table

INSERT INTO "users" ("created_at", "id", ...) VALUES ($1, $2, ...) [["created_at", Thu, 13 Oct 2016 23:53:17 CEST +02:00], ["id", nil], ...]

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

DETAIL: Failing row contains (null, ...)

My schema looks like following

create_table "users", :id => false, :force => true do |t|
   t.integer  "id",          :null => false  
   t.datetime "created_at",  :null => false
   ...
end

How can I solve this issue?

Answer 1

I have tried following solutions to solve this issues, I am writing down those for who need it.

Solution#1

drop my local database

rake db:drop

run migrations

rake db:migrate

now import the production database with the --data-only option

pg_restore --data-only --verbose --no-acl --no-owner -h localhost -U user_name -d database_name latest.dump

It's partially solve my problem but lose all my data in the table, so this is not an suitable one.

Solution#2 (working fine)

After observing the errors I understand some things like primary key sequence is break and and system should not try to insert null value to the ID field and some how my model forgot the primary key. and then I set the primary key through model and its working fine.

class User < ActiveRecord::Base
  self.primary_key = 'id'
end