Re: 8.2/8.3 incompatibility

In article <20080207095031(dot)O22500(at)megazone(dot)bigpanda(dot)com>,
Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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.