Can't rename an ActiveRecord table a second time. "[table]_pkey" does not exist

Some time ago, I ran the following rails migration to change the name of a table:

class RenameMaterialDonationsToMaterialDonationRequests < ActiveRecord::Migration
  def change
    rename_table :material_donations, :material_donation_requests
  end
end

Now I need to rename the table again. Here's my migration:

class RenameMaterialDonationRequestsToHelpRequests < ActiveRecord::Migration
  def change
    rename_table :material_donation_requests, :help_requests
  end
end

However, when I run the migration, I'm getting the following error:

PG::Error: ERROR:  relation "material_donation_requests_pkey" does not exist
: ALTER INDEX "material_donation_requests_pkey" RENAME TO "help_requests_pkey"/Users/[me]/.rvm/gems/ruby-2.1.4/gems/activerecord-4.2.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `exec'

I'm using Postgresql. In pgAdmin3, I can see that the pkey still retains the table name from before the first migration:

CONSTRAINT material_donations_pkey PRIMARY KEY (id)

How can I fix this to rename the table?

The answer below might work, but I decided to go with this:

execute "ALTER INDEX material_donations_pkey RENAME TO material_donation_requests_pkey;"

I chose this because it is the command that the migration was trying to run automatically as part of the original migration. That command wasn't automatically part of the pre-4.0 rails when I renamed this table the first time so I ran it now. I felt more comfortable doing exactly what rails is doing presently.

Can, Can was the leading avant-garde rock group of the 70s. Can experimented with noise, synthesizers, non-traditional music, cut-and-paste techniques, and, most� Can definition, to be able to; have the ability, power, or skill to: She can solve the problem easily, I'm sure. See more.

Here is a hacky solution, add another migration file and do this before change table name second time:

execute "ALTER TABLE material_donation_requests DROP CONSTRAINT material_donations_pkey;"
execute "ALTER TABLE material_donation_requests ADD PRIMARY KEY (id);"

And you probably need to do something similar after your next name changing.

The Official CAN / Spoon Records Website, Can may refer to: Contents. 1 Containers; 2 Music; 3 Other; 4 See also. Containers[edit]. Aluminum can � Drink can � Oil can � Steel and tin cans � Trash can� Define can. can synonyms, can pronunciation, can translation, English dictionary definition of can. to be able to, have the power or skill to: I can take a bus to the

Adding my solution for similar problem. Below is the error while running migrations

01 PG::UndefinedTable: ERROR:  relation "fundraise_stories_pkey" does not exist
01 : ALTER INDEX "fundraise_stories_pkey" RENAME TO "fundraisers_pkey"

Connected to database via pgadmin and selected constraints for the table fundraise_stories. It was showing constraint name as "fundrise_stories_pkey". So it was some old mistake because constraint name doesnt match with table name.

Solution:

  1. Find existing index name under constraints section for the table. In my case it was 'fundrise_stories_pkey'
  2. Rename the index before renaming table
  3. Finally, Rename the table.

Below is modified migration to rename index before renaming table.

def self.up
    execute "ALTER INDEX fundrise_stories_pkey RENAME TO fundraise_stories_pkey;"
    rename_table :fundraise_stories, :fundraisers
end

Log

D, [2020-02-02T17:16:27.428294 #7363] DEBUG -- :    (0.2ms)  BEGIN
== 20200127102616 RenameFundraiseStoryTableToFundraisers: migrating ===========
-- execute("ALTER INDEX fundrise_stories_pkey RENAME TO fundraise_stories_pkey;")
D, [2020-02-02T17:16:27.434366 #7363] DEBUG -- :    (5.5ms)  ALTER INDEX fundrise_stories_pkey RENAME TO fundraise_stories_pkey;
   -> 0.0061s
-- rename_table(:fundraise_stories, :fundraisers)
D, [2020-02-02T17:16:27.435722 #7363] DEBUG -- :    (0.7ms)  ALTER TABLE "fundraise_stories" RENAME TO "fundraisers"
D, [2020-02-02T17:16:27.438769 #7363] DEBUG -- :    (0.3ms)  ALTER TABLE "public"."fundraise_stories_id_seq" RENAME TO "fundraisers_id_seq"
D, [2020-02-02T17:16:27.439334 #7363] DEBUG -- :    (0.2ms)  ALTER INDEX "fundraise_stories_pkey" RENAME TO "fundraisers_pkey"
D, [2020-02-02T17:16:27.445452 #7363] DEBUG -- :    (0.8ms)  ALTER INDEX "index_fundraise_stories_on_bank_account_id" RENAME TO "index_fundraisers_on_bank_account_id"
D, [2020-02-02T17:16:27.446153 #7363] DEBUG -- :    (0.3ms)  ALTER INDEX "index_fundraise_stories_on_creator_id_and_creator_type" RENAME TO "index_fundraisers_on_creator_id_and_creator_type" 
   -> 0.0131s
== 20200127102616 RenameFundraiseStoryTableToFundraisers: migrated (0.0193s) ==

Can, can meaning: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more. can definition: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more.

Can (band), 1.4Used to indicate that something is typically the case. 'antique clocks can seem out of place in modern homes'. More example sentences. 110 synonyms of can from the Merriam-Webster Thesaurus, plus 97 related words, definitions, and antonyms. Find another word for can. Can: to bring (as an action or operation) to an immediate end.

CAN, VerbEdit. can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in adjectival use) couth). can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in adjectival use) couth) (auxiliary verb, defective) To know how to; to be able to.

Can, Word forms � 1. countable noun A can is a metal container in which something such as food, drink, or paint is put. � 2. verb [usually passive] When food or drink is� can (someone) To summarily dismiss or oust someone from employment; to fire someone. Management promptly canned the new accountant after his miscalculation cost the company hundreds of thousands of dollars. They'll can me if they ever find out I came into work drunk yesterday.

Comments
  • Works a charm! Came across this exact issue today while pushing to production (despite dev, test and stage all working fine) - ran this command to rename the index from the console and the migrations then ran fine. I owe you a very cold beer.
  • You're right, that does seem hacky. It seems like rails is trying to alter the pkey now but is not finding the right name. Shouldn't it have changed the pkey on the first migration? Also, will the DROP and ADD commands alter any data? The statement that seems to be failing in the migration is ALTER INDEX ... RENAME. Is that something I can do in a migration? And would that affect my data?
  • Actually you should change the primary key immediately once you changed the table name. Do backup first before you do any migration risky.
  • Understood. I think the reason this is happening is I was on rails 4.0.x when I did the migration, and there was a bug filed that renaming a table didn't update the pkey name. But I believe it's been "fixed" and hence now it's trying to update it, but can't because it's looking for the wrong name (the one that wasn't updated last time). Is there any reason I shouldn't just use the command that the migration is trying to use but can't? That is: ALTER INDEX "material_donation_requests_pkey" RENAME TO "help_requests_pkey"