foreign key with char and varchar

[Thomas Poty <thomas.poty@xxxxxxxxx>]

Hi world,

I wanted to test if char and varchar can be cross-referenced as foreign key. So i did these tests :

1) Can a varchar(7) reference a char(2) ? PostgreSQL accepts it
create table t1 (id char(2) primary key, data text);
create table t2 (id char(2) primary key, data text, id_t1 varchar(7) references t1 (id));

2) Can a varchar(7) reference a char(7) ? PostgreSQL accepts it
create table t3 (id char(7) primary key, data text);
create table t4 (id char(7) primary key, data text, id_t3 varchar(7) references t3 (id));

3) Can a char(2) reference a varchar(7) ? PostgreSQL accepts it.
create table t5 (id varchar(7) primary key, data text);
create table t6 (id char(7) primary key, data text, id_t5 char(2) references t5 (id));

3.1) I am very surprised to be able do that :
 -> insert into t5 (id,data) values ('1','toto');

     It works that is normal
 -> insert into t6 (id,data,id_t5) values ('1','toto','1');

    It works and it is a suprise by knowing char are padded with spaces so PostgreSQL would  compare '1' with '1 ' ( 1 with a space) and return false.

    Can you explain why it is working ???

3.2) I am very surprised to be able do that :
->  insert into t5 (id,data) values ('2 ','tata');

     It works and it is normal
 > insert into t6 (id,data,id_t5) values ('2','tata','2');
    ERROR:  insert or update on table "t6" violates foreign key constraint "t6_id_t5_fkey"
    DETAIL:  Key (id_t5)=(2 ) is not present in table "t5".
    It works and it is a suprise by knowing char are padded with spaces so PostgreSQL would  compare '2 ' with '2 ' ( 2 with a space) and return true.
    Can you explain why it is working ???

4) Can a char(7) reference a varchar(7) ? PostgreSQL accepts it

create table t7 (id varchar(7) primary key, data text);
create table t8 (id varchar(7) primary key, data text, id_t7 char(7) references t7 (id));

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.

Thanks for answers

Thomas