Thursday, July 30, 2015

PG::DuplicateColumn: ERROR: column already exists

Error:

PG::DuplicateColumn: ERROR:  column of relation table_name already exists

I am using Ruby On Rails with Postgresql and I faced this issue while I am doing migration.

sachin@sachin:~/demo_app$ rake db:migrate
== 20150710135824 AddStatusToUser: migrating =========================
-- add_column(:users, :status, :integer)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::DuplicateColumn: ERROR:  column "status" of relation "users" already exists
: ALTER TABLE "users" ADD COLUMN "status" integer/home/sachin/.rvm/gems/ruby-2.0.0-p598@demo_app/gems/activerecord-4.1.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `async_exec'

Solution:

Error indicates that in users table status column is already available. Then I found that earlier I have added same migration and then I have done rake db:migrate that time. So That time this column is created. And now I have added again. That's why I am facing this issue.

So here solution is: We need to remove existing column and then need to do migration. 

For that I have found this ways to resolve this issue:

1.) With ruby on rails,

        Open terminal and run "rails db" command it will connect with database. Then you can drop that added columns from here.

        You need to run this command for alter table:
        ALTER TABLE users DROP COLUMN status;

2.) Open postgresql console for drop column. 
         
         Open terminal and run "psql -U postgres" command then it will open psql console.Then you can drop that added columns from here.

        You need to run this command for alter table:
        ALTER TABLE users DROP COLUMN status;

3.) Add condition in migration

        if column_exists? :table_name, :column_name
                remove_column :table_name, :column_name
        end