How to count how many entries in a column are numeric using PostgreSQL

Question

I am trying to count how many entries are in a column that are both numeric and fulfill other conditions. I understand how that script is meant to look in SQL:

    SELECT COUNT(ingredients)
    FROM data.pie
    WHERE description LIKE 'cherry'
      AND is.numeric(price) = true

But I'm not sure how to translate that into a PostgreSQL script. Any help would be appreciated.

Thank you.

Answer 1

Another alternative to the one shown by Tim is to create a function ...

CREATE OR REPLACE FUNCTION is_numeric(val VARCHAR) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
    x = val::NUMERIC;
    RETURN TRUE;
    EXCEPTION WHEN OTHERS THEN    
    RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;

.. that can be used like this:

db=# SELECT is_numeric('foo'), is_numeric('1'), is_numeric('1.39');
 is_numeric | is_numeric | is_numeric 
------------+------------+------------
 f          | t          | t
(1 Zeile)