Hi.

-- PostgreSQL 9.6
-- CentOS 6.5
-- User: postgres

I have many queries.
in that case I need compare character varying data type (may be a table field) with integer value.

--Result is True or False
select '10' = 10;
select '10' = '10';
select '10'::character varying = '10'::character varying;
select '10'::character varying = 'foo bar';
select '10'::character varying = 'foo bar'::character varying;
select 'foo bar' = 'foo bar';
select '10'::character varying = '10';

--Result is "operator does not exist: character varying = integer"
select '10'::character varying = 10;

so i create a custom operator for compare character varying and integer.
step 1: create simple funtion
CREATE OR REPLACE FUNCTION public.is_equal_char_int(character varying, integer)
RETURNS boolean AS
$BODY$
BEGIN
IF $1 = $2::character varying THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
End;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


step 2: create new operator
CREATE OPERATOR public.=(
PROCEDURE = is_equal_char_int,
LEFTARG = character varying,
RIGHTARG = integer);


so i resoleved my problem and
select '10'::character varying = 10;
return true value.

and new problem is:
when i compare character varying value with unkown data type value, postgresql use my custom operator.
select '10'::character varying = 'foo bar'
result is :
invalid input syntax for integer: "foo bar"

select pg_typeof('foo bar')
return unkown data type.
and next step I create new operator for compare character varying and unkown data type.
Step 1:
CREATE OR REPLACE FUNCTION public.is_equal_char_unknown(
character varying, unknown)
RETURNS boolean AS
$BODY$
BEGIN
IF $1 = $2::character varying THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
End;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


step 2:
CREATE OPERATOR public.=(
PROCEDURE = is_equal_char_unknown,
LEFTARG = character varying,
RIGHTARG = unknown);


when I run
select '10'::character varying = 'foo bar'
I give
ERROR: operator is not unique: character varying = unknown.

So I'm in a hole.