foreign key with char and varchar

From: Thomas Poty <thomas(dot)poty(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: foreign key with char and varchar
Date: 2017-08-10 07:37:43
Message-ID: CAN_ctng6cBVZgaU6hzVzcRHdvTmQfHx4HA4ZrzLoZZRo7HVejQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2017-08-10 13:00:16 Re: pg_stat_statements -- Historical Query
Previous Message Julien Rouhaud 2017-08-10 07:30:54 Re: pg_stat_statements -- Historical Query