On 3/19/24 00:02, Rajesh S wrote:
Sorry Adrian, my bad. I'd mentioned "deposit_sub_no='1'" by mistake,
actually it was "deposit_sub_no=cast(1 as varchar)". This was throwing
1) Maybe you could explain the logic of taking a number and casting it
to a string to compare it to a number?
2) select 1::varchar = 1::varchar;
?column?
----------
t
So:
deposit_sub_no::varchar = 1::varchar
error "SQL Error [42883]: ERROR: operator does not exist: numeric =
character varying Hint: No operator matches the given name and argument
types. You might need to add explicit type casts. Position: 19". Then
realized that "numeric=text" works but "numeric=varchar" does not. I
could resolve the problem by creating the following function and
operator by commenting "CUMMUTATOR" and "NEGATOR". Also the error
"operator is only a shell" also vanished. I'm just sharing the script
for your reference. Thank you very much for your valuable support.
CREATE OR REPLACE FUNCTION public.num_eq_varchar(
numeric,
varchar)
RETURNS boolean
AS 'select case when $2 ~ ''^[0-9\.]+$'' then $1 operator(pg_catalog.=)
cast($2 as numeric) else $1::varchar = $2 end;'
LANGUAGE SQL IMMUTABLE;
-- Operator: =;
-- DROP OPERATOR IF EXISTS public.= (numeric , varchar);
CREATE OPERATOR public.= (
FUNCTION = num_eq_varchar,
LEFTARG = numeric,
RIGHTARG = varchar,
-- COMMUTATOR = =,
-- NEGATOR = <>,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES, MERGES
);
Thanks,
Rajesh S
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
