PG Bug reporting form <noreply@postgresql.org> writes:
> I have two tables that share the same primary key composed of three fields.
> When I perform an inner join using all the key fields in the statement, it
> returns non-existent values. The inner join shouldn't return any rows, but
> instead it returns a row with a non-existent value.
> Select rp.codactividadeconomica
> from p_contratos pc
> inner join remuner.remunerp_contratos rp
> on pc.codigoempresa = rp.codigoempresa
> and pc.concoanio = rp.concoanio
> and pc.concosecue = rp.concosecue
> where pc.codigoempresa = 2 and pc.concoanio = 16 and pc.concosecue =
> '017';
I'm inclined to guess that this is a symptom of a corrupted index;
does REINDEX help?
Since one of the columns in question is text (well, char(N)),
a plausible theory about what caused the corruption is an update
of the underlying system that resulted in a collation change.
See
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane
