getting an error 'function is_numeric(character varying) does not exist. ' in postgres

newtoJava 0
Viewed 37

I have created a function in postgres with the below query but it says "function is_numeric(character varying) does not exist." while executing the function.

I tried with "isnumeric" also, but no luck. same message.

Is there any alternate way to check whether the data value is numeric or not.

 v_stmt := 'update '||user$get_name_table( id )||
                  ' set err = err+1,err_val = err_val+1
                  where is_numeric('||u.column_name||')=0' ;
database-administration sql postgresql
1 Answers

You can write your own function, for example:

CREATE OR REPLACE FUNCTION is_numeric(pval text)
 RETURNS bool 
 LANGUAGE plpgsql
AS $function$   
declare 
    vval float8;
begin 
    select pval::float8 into vval;
    return true; 
exception 
    when others then 
        return false;
end 
$function$
;

select is_numeric('123') =>> true 
select is_numeric('25.8654') =>> true 
select is_numeric('H65D') =>> false
Ramin Faracov0
Related