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

If anyone else wonders How to just get data type of a varible (not column) you can use the pg_typeof(any) function.

Simply

SELECT pg_typeof(your_variable);

OR

SELECT pg_typeof('{}'::text[]); //returns text[];

Note

pg_typeof(varchar_column) will return character varying regardless of the content of the column. Any column or variable is already typed and pg_typeof will return that declared type. It will not find the "best fitting" type depending on the value of that column (or variable). -- quote from a_horse_with_no_name's comment.

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;