title: isnumeric() with PostgreSQL
body: <p>I need to determine whether a given string can be interpreted as a number (integer or floating point) in an SQL statement. As in the following:</p>
  
  <pre><code>SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test
  </code></pre>
  
  <p>I found that Postgres' <a href="http://www.postgresql.org/docs/9.2/static/functions-matching.html" rel="noreferrer">pattern matching</a> could be used for this. And so I adapted the statement given in <a href="http://www.postgresql.org/message-id/003601c42ec7$e7806ee0$8401a8c0@home.axiontech.com" rel="noreferrer">this place</a> to incorporate floating point numbers. This is my code:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The output:</p>
  
  <pre><code>    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)
  </code></pre>
  
  <p>As you can see, the first two items (the empty string <code>''</code> and the sole period <code>'.'</code>) are misclassified as being a numeric type (which they are not). I can't get any closer to this at the moment. Any help appreciated!</p>
  
  <hr>
  
  <p><strong>Update</strong> Based on <a href="https://stackoverflow.com/a/16196091/1025391">this answer</a> (and its comments), I adapted the pattern to:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Which gives:</p>
  
  <pre><code>     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)
  </code></pre>
  
  <p>There are still some issues with the scientific notation and with negative numbers, as I see now.</p>