Postgres 数据类型转换 [英] Postgres data type cast

问题描述

我的数据库是 Postgres 8.我需要将数据类型转换为另一种.这意味着，列数据类型之一是 varchar 并且需要在 SELECT 语句中使用 Postgres 将其转换为 int.

My database is Postgres 8. I need to cast data type to another. That means, one of columns data type is varchar and need to cast it into int with Postgres in a SELECT statement.

目前，我获取字符串值并将其转换为 Java 中的 int.
有什么办法吗?示例代码将不胜感激.

Currently, I get the string value and cast it into int in Java.
Is there any way to do it? Sample code would be highly appreciated.

推荐答案

cast(varchar_col AS int)  -- SQL standard

或

varchar_col::int          -- Postgres syntax shorthand

这些语法变体(几乎)在任何地方都是有效的.第二个在特殊情况下可能需要嵌套括号:

Theses syntax variants are valid (almost) anywhere. The second may require nesting parentheses in special situations:

• PostgreSQL:在长度上创建索引所有表格字段

如果语法限制只允许使用功能符号，则可能需要第一个:

And the first may be required where only functional notation is allowed by syntax restrictions:

• PostgreSQL - LATERAL 表函数上的 CAST 与 :: 运算符

还有两种变体:

int4(varchar_col)         -- only works for some type names
int '123'                 -- must be an untyped, quoted string literal

注意我是如何写int4(varchar_col)的.那是内部类型名称，并且还为它定义了一个函数.不能作为 integer() 或 int() 工作.

Note how I wrote int4(varchar_col). That's the internal type name and there is also a function defined for it. Wouldn't work as integer() or int().

另请注意，最后一种形式不适用于 array 类型.int[] '{1,2,3}' 必须是 '{1,2,3}'::int[]或 cast('{1,2,3}' AS int[]).

Note also that the last form does not work for array types. int[] '{1,2,3}' has to be '{1,2,3}'::int[] or cast('{1,2,3}' AS int[]).

手册中的详细信息此处 和 这里.

Details in the manual here and here.

要对 integer 有效，字符串必须由可选的前导符号 (+/-) 后跟数字组成.忽略前导/尾随空格.

To be valid for integer, the string must be comprised of an optional leading sign (+/-) followed by digits only. Leading / trailing white space is ignored.

这篇关于Postgres 数据类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！