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
If anyone else wonders How to just get data type of a variable (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.
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;
For a postgres DB, you can get all the types and their enum values using this query -
SELECT t.typname AS enum_name,e.enumlabel AS enum_value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'e'
ORDER BY enum_name;
