POSTGRESQL 处理隐行转换

 

问题背景：
两张表需要做关联，但A表的字段是INT类型， 而B表的是VARCHAR类型。

模拟：
rocket2_i5=# create table t_int(id int4);
CREATE TABLE
rocket2_i5=# create table t_var(id varchar(9));
CREATE TABLE
rocket2_i5=# insert into t_int values(1);
INSERT 0 1
rocket2_i5=# insert into t_var values('1');
INSERT 0 1
rocket2_i5=# select * from t_int a inner join t_var b on a.id=b.id;
ERROR:  operator does not exist: integer = character varying
LINE 1: select * from t_int a inner join t_var b on a.id=b.id;                                                  
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
解决办法， 使用CAST函数：

rocket2_i5=# select * from t_int a inner join t_var b on a.id=cast(b.id as int);
 id | id 
----+----
  1 | 1
(1 row)
rocket2_i5=# select * from t_int a inner join t_var b on cast(a.id as varchar)=b.id;
 id | id 
----+----
  1 | 1
(1 row)