The order of the arguments matter. You need an operator that expects a varchar on the left hand side of the operator, and numeric on the right side. For example:
cast_test=# create table deposit_lien(deposit_no varchar);
CREATE TABLE
create database cast_test;
\c cast_test
You are now connected to database "cast_test" as user "greg".
CREATE TABLE
cast_test=# select * from deposit_lien where deposit_no='0002114029832'::numeric;
ERROR: operator does not exist: character varying = numeric
ERROR: operator does not exist: character varying = numeric
cast_test=# create function varchar_eq_numeric(varchar,numeric)
cast_test-# returns bool language sql immutable as $$ select $1::numeric=$2::numeric $$;
CREATE FUNCTION
cast_test-# returns bool language sql immutable as $$ select $1::numeric=$2::numeric $$;
CREATE FUNCTION
cast_test=# create operator public.= (function = varchar_eq_numeric,leftarg = varchar,rightarg = numeric);
CREATE OPERATOR
CREATE OPERATOR
cast_test=# select * from deposit_lien where deposit_no='0002114029832'::numeric;
deposit_no
------------
(0 rows)
deposit_no
------------
(0 rows)
But before creating the function and operator it was working fine
Was it? It helps to show us the exact things ran and the exact output, rather than just "it was working fine" :)
Cheers,
Greg
