ADD FOREIGN KEY fails, but the records exist

On 2/15/21 8:12 AM, Ron wrote:

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?

(We're migrating from Oracle to PostgreSQL, and this is working in Oracle.)

sides=> ALTER TABLE employer_response
    ADD CONSTRAINT amended_response_fk FOREIGN KEY
(amended_response_id, part_date)
        REFERENCES employer_response(employer_response_id, part_date)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response".
sides=>
sides=> select employer_response_id, part_date
sides-> from strans.employer_response
sides-> where amended_response_id = 103309154;
employer_response_id |      part_date
----------------------+---------------------
            103309156 | 2021-01-06 00:00:00
(1 row)

The error:

DETAIL: Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id = employer_response_id.
You are showing an employer_response_id of 103309156

sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where amended_response_id = 103309154;
employer_response_id |      part_date
----------------------+---------------------
            103309156 | 2021-01-06 00:00:00
(1 row)

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

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?

(We're migrating from Oracle to PostgreSQL, and this is working in Oracle.)

sides=> ALTER TABLE employer_response
     ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id,
part_date)
         REFERENCES employer_response(employer_response_id, part_date)
     ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response".
sides=>
sides=> select employer_response_id, part_date
sides-> from strans.employer_response
sides-> *where amended_response_id = 103309154*;
employer_response_id |      part_date
----------------------+---------------------
             103309156 | 2021-01-06 00:00:00
(1 row)

The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id = employer_response_id.
You are showing an employer_response_id of 103309156

But my query's WHERE clause specifies "amended_response_id = 103309154;" 
(I've highlighted it, if you have a GUI MUA.)

sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where *amended_response_id = 103309154; *
employer_response_id |      part_date
----------------------+---------------------
             103309156 | 2021-01-06 00:00:00
(1 row)

--
Angular momentum makes the world go 'round.

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id =
employer_response_id. You are showing an employer_response_id of
103309156

But my query's WHERE clause specifies "amended_response_id =
103309154;"  (I've highlighted it, if you have a GUI MUA.)

Yes but amended_response_id is referencing employer_response_id. So do
you have a record that matches:

employer_response_id part_date

103309154 2021-01-06 00:00:00

sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where *amended_response_id = 103309154; *
employer_response_id |      part_date
----------------------+---------------------
             103309156 | 2021-01-06 00:00:00
(1 row)

--
Angular momentum makes the world go 'round.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id = employer_response_id.
You are showing an employer_response_id of 103309156

But my query's WHERE clause specifies "amended_response_id = 103309154;" 
(I've highlighted it, if you have a GUI MUA.)

Yes but amended_response_id is referencing employer_response_id. So do you
have a record that matches:

employer_response_id   part_date

103309154              2021-01-06 00:00:00

Ah, I see now.  No, we don't/

sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where *amended_response_id = 103309154; *
employer_response_id |      part_date
----------------------+---------------------
             103309156 | 2021-01-06 00:00:00
(1 row)

--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.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

On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id = employer_response_id.
You are showing an employer_response_id of 103309156

But my query's WHERE clause specifies "amended_response_id = 103309154;" 
(I've highlighted it, if you have a GUI MUA.)

Yes but amended_response_id is referencing employer_response_id. So do you
have a record that matches:

employer_response_id   part_date

103309154              2021-01-06 00:00:00

The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
    ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id,
part_date)
        REFERENCES employer_response(employer_response_id, part_date)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response".
sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
            103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
*103309154* |                     | 2021-01-06 *15:14:03*
(1 row)

--
Angular momentum makes the world go 'round.

On 2/15/21 8:55 AM, Ron wrote:

On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id =
employer_response_id. You are showing an employer_response_id of
103309156

But my query's WHERE clause specifies "amended_response_id =
103309154;"  (I've highlighted it, if you have a GUI MUA.)

Yes but amended_response_id is referencing employer_response_id. So do
you have a record that matches:

employer_response_id   part_date

103309154              2021-01-06 00:00:00

The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
    ADD CONSTRAINT amended_response_fk FOREIGN KEY
(amended_response_id, part_date)
        REFERENCES employer_response(employer_response_id, part_date)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response".
sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
            103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
*103309154* |                     | 2021-01-06 *15:14:03*
(1 row)

Well since it is the same column(type) then it had to be something in
the transfer of the data from Oracle to Postgres. What are the values on
the Oracle end?

--
Angular momentum makes the world go 'round.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2/15/21 10:58 AM, Adrian Klaver wrote:

On 2/15/21 8:55 AM, Ron wrote:

On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response"

is pointing at 103309154 for amended_response_id =
employer_response_id. You are showing an employer_response_id of
103309156

But my query's WHERE clause specifies "amended_response_id =
103309154;"  (I've highlighted it, if you have a GUI MUA.)

Yes but amended_response_id is referencing employer_response_id. So do
you have a record that matches:

employer_response_id   part_date

103309154              2021-01-06 00:00:00

The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
     ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id,
part_date)
         REFERENCES employer_response(employer_response_id, part_date)
     ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response".
sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
             103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
*103309154* |                     | 2021-01-06 *15:14:03*
(1 row)

Well since it is the same column(type) then it had to be something in the
transfer of the data from Oracle to Postgres. What are the values on the
Oracle end?

That's a good question, which I don't know the answer to.

--
Angular momentum makes the world go 'round.

On 2/15/21 8:55 AM, Ron wrote:

The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
    ADD CONSTRAINT amended_response_fk FOREIGN KEY
(amended_response_id, part_date)
        REFERENCES employer_response(employer_response_id, part_date)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06
00:00:00) is not present in table "employer_response".
sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
            103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
----------------------+---------------------+---------------------
*103309154* |                     | 2021-01-06 *15:14:03*
(1 row)

To add to my previous post regarding the part about the data transfer
process. You might look for code that did something like:

select current_date::timestamp;

current_date
---------------------
2021-02-15 00:00:00

In other words turned a date into a timestamp.

--
Angular momentum makes the world go 'round.

--
Adrian Klaver
adrian.klaver@aklaver.com