Re: ADD FOREIGN KEY fails, but the records exist

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(dot)klaver(at)aklaver(dot)com