Search Postgresql Archives

Re: ADD FOREIGN KEY fails, but the records exist

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux