isnumeric() with PostgreSQL

As you may noticed, regex-based method is almost impossible to do correctly. For example, your test says that 1.234e-5 is not valid number, when it really is. Also, you missed negative numbers. What if something looks like a number, but when you try to store it it will cause overflow?

Instead, I would recommend to create function that tries to actually cast to NUMERIC (or FLOAT if your task requires it) and returns TRUE or FALSE depending on whether this cast was successful or not.

This code will fully simulate function ISNUMERIC():

CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$DECLARE x NUMERIC;BEGIN    x = $1::NUMERIC;    RETURN TRUE;EXCEPTION WHEN others THEN    RETURN FALSE;END;$$STRICTLANGUAGE plpgsql IMMUTABLE;

Calling this function on your data gets following results:

WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),  ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))SELECT x, isnumeric(x) FROM test;    x     | isnumeric----------+-----------          | f .        | f .0       | t 0.       | t 0        | t 1        | t 123      | t 123.456  | t abc      | f 1..2     | f 1.2.3.4  | f 1x234    | f 1.234e-5 | t (13 rows)

Not only it is more correct and easier to read, it will also work faster if data was actually a number.

You problem is the two 0 or more [0-9] elements on each side of the decimal point. You need to use a logical OR | in the number identification line:

~'^([0-9]+\.?[0-9]*|\.[0-9]+)$'

This will exclude a decimal point alone as a valid number.

I suppose one could have that opinion (that it's not a misuse of exception handling), but generally I think that an exception handling mechanism should be used just for that. Testing whether a string contains a number is part of normal processing, and isn't "exceptional".

But you're right about not handling exponents. Here's a second stab at the regular expression (below). The reason I had to pursue a solution that uses a regular expression was that the solution offered as the "correct" solution here will fail when the directive is given to exit when an error is encountered:

SET exit_on_error = true;

We use this often when groups of SQL scripts are run, and when we want to stop immediately if there is any issue/error. When this session directive is given, calling the "correct" version of isnumeric will cause the script to exit immediately, even though there's no "real" exception encountered.

create or replace function isnumeric(text) returns boolean  immutable  language plpgsqlas $$begin  if $1 is null or rtrim($1)='' then    return false;  else    return (select $1 ~ '^ *[-+]?[0-9]*([.][0-9]+)?[0-9]*(([eE][-+]?)[0-9]+)? *$');  end if;end;$$;