Thomas Poty <thomas.poty@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
