Re: foreign key with char and varchar

Thomas Poty <thomas(dot)poty(at)gmail(dot)com> writes:
> I wanted to test if char and varchar can be cross-referenced as foreign
> key. So i did these tests :
> ...
> I thought the columns referring and referenced had to be the same data type
> with the same length but it seems not to be the case.

Looking into the code, I see that the actual rules are that the FK
comparisons are done using the equality semantics of the referenced (PK)
column, so long as there is an implicit coercion available from the
referencing (FK) column type. So the comparisons are done as though
by t6.id_t5::varchar = t5.id, which is perhaps a bit surprising
because if you just write "WHERE t6.id_t5 = t5.id" you would get the
opposite coercion, t6.id_t5 = t5.id::char. (The first case will strip
trailing spaces from the char value but treat trailing spaces in the
varchar value as significant; the second case will consider trailing
spaces insignificant on both sides.) But it more or less has
to be this way, because the foreign key constraint makes no sense
at all unless it has the same notion of equality as does the unique
index on the PK column. Otherwise there could be more than one PK
row that "matches" an FK row.

If this is explained anywhere in the user-facing documentation,
I didn't find it in a quick look :-(

regards, tom lane