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@aklaver.com