Re: Foreign keys for non-default datatypes

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I'd argue that this case makes sense in a purely theoretical sense,
> numeric(8,0) references int is a sensible operation (when you don't take
> into account specific PostgreSQL limitations) and it's required by spec,
> but I agree that promotion on the PK side is problematic because of the
> issues about equality so we may not be able to do better. I'm just worried
> that we're moving further from compliance with the spec.

Well, if anyone complains about it we can add an int-eq-numeric operator
to the btree opclass for int, but the case seems pretty pointless to me.
Why are you using a numeric if the FK constraint is going to keep you
from storing any values that don't fit in int?  (Cases where this
argument doesn't hold will fit under the its-ok-to-promote-the-FK-side
rule.)

The reason I'm hesitant to add a bunch more cross-type operators is
mainly that we have too darn many operators named "=" already.  I've
seen in recent profiling tests that it's taking the parser a noticeable
amount of time to decide which one is meant.  So I don't want to add a
lot more without a fairly credible use-case, and right now this doesn't
look like one to me.

BTW, in researching this I noted that SQL92 requires the referencing
column to be the *same* type as the referenced column (11.8 <referential
constraint definition> syntax rule 7).  I see that SQL99 relaxes this to
require only comparable types, but I'm not really going to feel too bad
if we restrict it to being just "sensible" combinations of comparable
types.
        regards, tom lane