Stephan Szabo <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match