Re: [SQL] Wierd stuff

wieck(at)debis(dot)com (Jan Wieck) writes:
> Mario Filipe wrote:
>>>> | concelho_naturalidade | int2
>>>> ...
>>>> | codigo | int4
>>>>
>>>> The field "concelho_naturalidade" on the first table has values taken
>>>> from the "codigo" field from the second.

> First of all, why aren't the two fields of the same data
> type? Shouldn't cause your problem, but it's bad database
> design anyway IMHO.

Actually, I think that this may indeed be the root of the problem.
I killed several bugs in cross-data-type joins a while back ... for
example, IIRC pre-6.5 Postgres thought it could use hash joins for
cross-data-type equality operators, but that doesn't work properly in
general. Mario, what does EXPLAIN show about how the join query is
being done?

If I'm right about this, then upgrading to 6.5.* should fix the problem.

regards, tom lane