Re: bug in 8.3? foreign key refers to different type

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Tue, 26 Feb 2008, craigp wrote:
>> These create table commands succeed, even tho the foreign key refers to
>> a 'different' type (int2 product_id column refers to an int8 column):

> The requirements in recent SQL specs appears to be that the column types
> are comparable, not the same.

The 8.3 code requires the two column datatypes to participate in the
same btree operator family, which is a fairly strong form of
"comparable". Offhand I think the available numeric-type families are
int2/int4/int8, float4/float8, and numeric.

> As far as I can tell the spec requires two numbers to be comparable, which
> would make a failure for numeric or real an actual bug, but I don't have
> an 8.3 system available at the moment to confirm against.

If anyone actually complains, we could possibly extend the set of
opclasses to cover more cases. I'm not real clear on the sanity of
linking (for instance) a float8 PK to a numeric FK, though. I think
you'd constantly be getting bit by precision issues.

regards, tom lane