In article <20080207095031.O22500@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 7 Feb 2008, Harald Fuchs wrote:
>> This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the
>> following (somewhat misleading) error message:
>>
>> ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fk"
>> DETAIL: Key (t1id)=(t1id1) is not present in table "t1".
> If the types were considered not comparable, you should have gotten a
> message to that effect rather than a not present message.
Yes, this was really confusing.
> More to the point that comparison should have succeeded I think.
Well, it did succeed in 8.2.x, and I'm actually grateful that 8.3.0
noticed the sloppiness on my side.
> What do the following give?
> select * from t1 where id=CAST('t1id1' as VARCHAR(5));
This returns t1id1, as expected.
> select * from ONLY t2 fk LEFT OUTER JOIN ONLY t1 pk ON
> (pk.id = fk.t1id) WHERE pk.id IS NULL;
This returns an empty result set.