I'm getting a failure trying to add a constraint that previously existed on a table after having dropped it to do an alter:

mcqueen=# ALTER TABLE ONLY public.mcqueen_base_imagemeta2
mcqueen-#     ADD CONSTRAINT mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b FOREIGN KEY (image_id) REFERENCES public.mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED;
ERROR:  insert or update on table "mcqueen_base_imagemeta2" violates foreign key constraint "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b"
DETAIL:  Key (image_id)=(5648463223) is not present in table "mcqueen_base_image".

I can select that image_id in the both referenced tables and the rows do exist.

I tried changing the PK on the row in mcqueen_base_image to a difference one and back and even deleting and re-adding the row, but neither worked. I finally deleted the row, but then failed on another. Since this table has some 7 billion rows, waiting 2 hours for each failure and deleting that row is not a workable solution.

Is there anything I can do to "repair" this issue?

UPDATE: The root issue ended up being some corruption in our backup restore process, so the test DB was just plain corrupt, so no re-indexing or even reloading of tables helped the issue.

  • What is your exact Postgres version? select version(); will show you.
    – user1822
    Commented Aug 27, 2019 at 7:15

1 Answer 1


If you get that message although the image_id is present in mcqueen_base_image, you must be dealing with data corruption.

Try to

REINDEX mcqueen_base_image;

and see if the problem persists.

If there are still problems, you'll have to repair the data manually. Then

  • dump and restore the database to a new cluster to get rid of any lingering data corruption.

  • investigate the cause of the corruption.

  • Tried re-index, dropping and re-creating the PK constraint, but without success. I am now dumping and reloading the table. Fingers crossed Commented Aug 30, 2019 at 22:06
  • Restore may fail to create the constraint. Then fix the data manually until it works. Commented Aug 30, 2019 at 22:11
  • Restore is how I got here. As in, this is a restored copy of the DB I was testing my alter on and encountered the problem. What I'm doing now is actually a COPY TO; TRUNCATE; COPY FROM Commented Aug 30, 2019 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.