Solución:
Como habrá notado, el método basado en expresiones regulares es casi imposible de hacer correctamente. Por ejemplo, su prueba dice que 1.234e-5 no es un número válido, cuando realmente lo es. Además, te perdiste los números negativos. ¿Qué pasa si algo parece un número, pero cuando intentas almacenarlo se desborda?
En su lugar, recomendaría crear una función que intente transmitir a NUMERIC (o FLOAT si tu tarea lo requiere) y regresa TRUE o FALSE dependiendo de si este elenco tuvo éxito o no.
Este código simulará completamente la función 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;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;
Llamar a esta función en sus datos obtiene los siguientes resultados:
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)
No solo es más correcto y más fácil de leer, sino que también funcionará más rápido si los datos fueran en realidad un número.
Tu problema son los dos 0 o más [0-9] elementos a cada lado del punto decimal. Necesitas usar un OR lógico | en la línea de identificación del número:
~'^([0-9]+.?[0-9]*|.[0-9]+)$'
Esto excluirá solo un punto decimal como número válido.