postgres=# create table test(t varchar(32));
CREATE TABLE
postgres=# insert into test values('1');
INSERT 0 1
postgres=# insert into test values('2');
INSERT 0 1
postgres=# insert into test values('3');
INSERT 0 1
postgres=# select * from test where t=2;
ERROR: operator does not exist: character varying = integer
LINE 1: select * from test where t=2;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# select * from test where t='2';
2
postgres=# insert into test values(4);
INSERT 0 1
postgres=# select * from test where t=4;
ERROR: operator does not exist: character varying = integer
LINE 1: select * from test where t=4;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=#
postgres=# select * from test where t=cast(2 as VARCHAR);
2
postgres=# create cast(integer as varchar) with inout as implicit;
CREATE CAST
postgres=# create cast(varchar as integer) with inout as implicit;
CREATE CAST
postgres=# select * from test where t=4;
4
postgres=# create cast(bigint as varchar) with inout as implicit;
CREATE CAST
postgres=# create cast(varchar as bigint) with inout as implicit;
CREATE CAST
postgres=# select * from test where t='2';
2
postgres=# select * from test where t=2;
ERROR: operator is not unique: character varying = integer
LINE 1: select * from test where t=2;
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
postgres=# select * from test where t=4;
ERROR: operator is not unique: character varying = integer
LINE 1: select * from test where t=4;
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
postgres=# create cast(integer as varchar) with inout as implicit;
ERROR: cast from type integer to type character varying already exists
postgres=# drop cast(bigint as varchar);
DROP CAST
postgres=# drop cast(varchar as bigint);
DROP CAST
postgres=# select * from test where t=4;
4
postgres=# drop cast(varchar asdrop cast(varchar as integer) ;
postgres(#
postgres(# ^C
postgres=# drop cast(varchar as integer);
DROP CAST
postgres=# select * from test where t=4;
ERROR: operator does not exist: character varying = integer
LINE 1: select * from test where t=4;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# select * from test where t=2;
ERROR: operator does not exist: character varying = integer
LINE 1: select * from test where t=2;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# create cast(integer as varchar) with inout as implicit;
ERROR: cast from type integer to type character varying already exists
postgres=# create cast(varchar as integer) with inout as implicit;
CREATE CAST
postgres=# select * from test where t=2;
2
postgres=# select * from test where t=4;
4
PostgreSQL 自定义自动类型转换(CAST)-阿里云开发者社区
