Re: ADD FOREIGN KEY fails, but the records exist

Ron <ronljohnsonjr(at)gmail(dot)com> writes:
> Postgresql 12.5
> It's a self-referential FK on a single (but partitioned) table.  The ALTER
> TABLE command fails, but I queried it, and the record that it fails on
> exists.  I modified the original INITIALLY IMMEDIATE clause to INITIALLY
> DEFERRED but that did not help.

> What am I doing wrong?

As Adrian noted, the queries you showed don't actually prove that the
required employer_response_id exists in the table. However, if the
identical data worked in Oracle then it should work in PG too, so for
the moment I'll assume that that was a thinko and the FK should be
valid. In that case I'd go looking for "invisible" reasons for the
keys not to match. You did not show the column data types, but if the
response ids are strings not numbers then I'd be wondering about extra
spaces and such. Perhaps Oracle is more forgiving of such things than
PG is.

regards, tom lane