how to check type of value in postgres

Question

I want to check type of value in postgres like this:

SELECT id,
       CASE 
         WHEN val_is_integer THEN (SOME_QUERY)
         WHEN val_isnot_integer THEN (ANOTHER_QUERY)
         ELSE 0
       END
  FROM test;

How to do that?

---

notes: the value is varchar type in table, and in that field there is value is numeric and varchar ...

example:

ID | value
1 | test
2 | 10
3 | 12
4 | test123

Answer 1

As @OMG Ponies points out, the data type has to be the same for all outcomes in a CASE expression. The documentation should indicate what happens when data types for different paths differ e.g. explicit conversion, perhaps based on data type precedence, and situations when you can expect an error.

e.g.

UNION and CASE Type Resolution

If all inputs are of type unknown, resolve as type text (the preferred type for string category). Otherwise, ignore the unknown inputs while choosing the type.

If the non-unknown inputs are not all of the same type category, fail.

Choose the first non-unknown input type which is a preferred type in that category or allows all the non-unknown inputs to be implicitly coerced to it.

Coerce all inputs to the selected type.

It goes on to give examples.

I guess that the concept of a "preferred type" is analogous to data type precedence.

Answer 2

Your value column is always of type varchar, it seems you want to check if the content is a number/integer.

You could do that by creating a function, e.g.

create function isdigits(text) returns boolean as '
select $1 ~ ''^(-)?[0-9]+$'' as result
' language sql;

(That function could probably be implemented by trying to cast the text to int, or using the int4() function and catching the error that occurs too, and return NULL.)

With such a function you could do:

SELECT id,
       CASE 
         WHEN value IS NULL THEN 0
         WHEN isdigits(value) THEN (SOME_QUERY)
         ELSE (ANOTHER_QUERY)
       END
  FROM test;