Stephan Szabo <[EMAIL PROTECTED]> writes: > There's a bit of difference between not encouraging it and disallowing it > entirely, but I'm willing to buy that argument. I do think that numeric > reference int needs to be allowed though, and I thought that's also > currently not there (although int reference numeric should work I think).
Um, not sure which way you mean there. The case that would work in the proposal as I gave it is where the referencing column's type is implicitly promotable to the referenced column's type. So for example an FK int column could reference a PK numeric column but (without more btre support) not an FK numeric column referencing a PK int. Is that what you meant? If you try numeric-references-int today, you get the "costly sequential scan" warning, because the selected "=" operator is numeric_eq and that's not compatible with the PK's index. Basically, if any implicit promotion happens on the PK side, you're going to get the warning because you're no longer using an "=" operator that matches the index. We have not seen many complaints about getting that warning since 8.0, so I think that in practice people aren't using these cases and it'd be OK to make them a hard error instead. I would also argue that if implicit promotion does happen on the PK side, it's very questionable what semantics the FK constraint has anyway --- you can no longer be sure that the operator you are using has a notion of equality that's compatible with the PK index's notion. 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