Обсуждение: ADD FOREIGN KEY fails, but the records exist
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) 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: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:
But my query's WHERE clause specifies "amended_response_id = 103309154;" (I've highlighted it, if you have a GUI MUA.)
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.
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.5The 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.
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