SQL 如何检查postgres中的值类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3827828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to check type of value in postgres
提问by de_3
I want to check type of value in postgres like this:
我想像这样检查 postgres 中的值类型：
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 ...
注意：表中的值为 varchar 类型，该字段中的值为数字和 varchar ...
example:
例子：
ID | value
1 | test
2 | 10
3 | 12
4 | test123
回答by ArchNoob
If anyone else wonders How to just get data type of a varible(not column) you can use the pg_typeof(any)function.
如果其他人想知道如何仅获取变量（而不是列）的数据类型，您可以使用该pg_typeof(any)函数。
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.
无论列的内容如何，pg_typeof(varchar_column) 都将返回不同的字符。任何列或变量已经被类型化并且 pg_typeof 将返回那个声明的类型。它不会根据该列（或变量）的值找到“最佳拟合”类型。--引用 a_horse_with_no_name 的评论。
回答by nos
Your value column is always of type varchar, it seems you want to check if the content is a number/integer.
您的值列始终为 varchar 类型，您似乎想检查内容是否为数字/整数。
You could do that by creating a function, e.g.
你可以通过创建一个函数来做到这一点，例如
create function isdigits(text) returns boolean as '
select ~ ''^(-)?[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.)
（该函数可能可以通过尝试将文本转换为 int 或使用 int4() 函数并捕获发生的错误并返回 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;
