如何在 postgres 中检查值的类型

我想检查一下 postgres 中的值类型，如下所示:

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

怎么做？

附注: Table 中的值是 varchar 类型，在该字段中有 value 是 numeric 和 varchar..。

例如:

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

94583 次浏览

小开

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;

小开

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);

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.