isnumeric () con PostgreSQL

Necesito determinar si una cadena dada se puede interpretar como un número (entero o punto flotante) en una statement de SQL. Como en lo siguiente:

SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test 

Descubrí que la coincidencia de patrones de Postgres podría usarse para esto. Y entonces adapté la statement dada en este lugar para incorporar números de coma flotante. Este es mi código:

 WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'), ('123.456'), ('abc'), ('1..2'), ('1.2.3.4')) SELECT x , x ~ '^[0-9]*.?[0-9]*$' AS isnumeric FROM test; 

La salida:

  x | isnumeric ---------+----------- | t . | t .0 | t 0. | t 0 | t 1 | t 123 | t 123.456 | t abc | f 1..2 | f 1.2.3.4 | f (11 rows) 

Como puede ver, los dos primeros elementos (la cadena vacía '' y el único período '.' ) Se clasifican erróneamente como de tipo numérico (que no lo son). No puedo acercarme más a esto en este momento. Cualquier ayuda apreciada!


Actualización Basándome en esta respuesta (y sus comentarios), adapté el patrón a:

 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 , x ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$' AS isnumeric FROM test; 

Lo que da:

  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 | f (13 rows) 

Todavía hay algunos problemas con la notación científica y con números negativos, como veo ahora.

Como habrás 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 números negativos. ¿Qué pasa si algo se parece a un número, pero cuando intenta almacenarlo causará un desbordamiento?

En cambio, recomendaría crear una función que intente realmente convertir a NUMERIC (o FLOAT si su tarea así lo requiere) y devuelve TRUE o FALSE dependiendo de si este lanzamiento fue exitoso 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; 

Al activar 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 fácil de leer, también funcionará más rápido si los datos fueran realmente un número.

Tu problema son los dos 0 o más elementos [0-9] en cada lado del punto decimal. Necesita usar un OR lógico | en la línea de identificación de número:

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

Esto excluirá un punto decimal solo como un número válido.