在Postgres 8.3中,是否可以通过将varchar列转换为integer来对结果行进行排序?
varchar
integer
k7fdbhmy1#
完全有可能。
ORDER BY varchar_col::int
字符串请确保varchar列中的每个条目都有有效的整数文字,否则将出现异常invalid input syntax for integer。前导和尾随白色是可以的-这是自动修剪的。如果是这样的话,那么为什么不开始将列转换为integer呢?更小、更快、更干净、更简单。
invalid input syntax for integer
如果输入可能不是有效的整数文字,请使用专用函数pg_input_is_valid()进行测试,而不会引发异常:
pg_input_is_valid()
ORDER BY CASE WHEN pg_input_is_valid(varchar_col, 'integer') THEN varchar_col::int END
型SQL CASE在没有ELSE的情况下默认为null-按默认升序排序最后。请参阅:
CASE
ELSE
null
新的内置功能比手动解决方案更快,更可靠。不同之处还在于它不尝试修复损坏的输入。
要在强制转换之前删除非数字字符,从而避免可能的异常,请执行以下操作:
ORDER BY NULLIF(regexp_replace(varchar_col, '\D', '', 'g'), '')::int
型
regexp_replace()
\D
[^[:digit:]]
[^0-9]
在旧的Postgres版本中,设置为过时的standard_conforming_strings = off,你必须使用Posix转义字符串语法E'\\D'来转义反斜杠\。这是Postgres 8.3的默认设置,所以你需要为你的过时版本。
standard_conforming_strings = off
E'\\D'
\
g
-
NULLIF
NULL
0
保证结果有效。这个过程是针对问题主体中要求的**integer**的转换,而不是标题中提到的numeric *。
numeric
一种方式是index on an expression。
CREATE INDEX tbl_varchar_col2int_idx ON tbl (cast(NULLIF(regexp_replace(varchar_col, '\D', '', 'g'), '') AS integer));
型然后在ORDER BY子句中使用相同的表达式:
ORDER BY
ORDER BY cast(NULLIF(regexp_replace(varchar_col, '\D', '', 'g'), '') AS integer)
型使用EXPLAIN ANALYZE测试函数索引是否被实际使用。
EXPLAIN ANALYZE
tjrkku2a2#
另外,如果你想按一个文本列排序,该列有可转换为float的内容,那么这就可以做到:
select * from your_table order by cast(your_text_column as double precision) desc;
字符串
2条答案
按热度按时间k7fdbhmy1#
完全有可能。
字符串
请确保
varchar
列中的每个条目都有有效的整数文字,否则将出现异常invalid input syntax for integer
。前导和尾随白色是可以的-这是自动修剪的。如果是这样的话,那么为什么不开始将列转换为
integer
呢?更小、更快、更干净、更简单。如何避免异常?
Postgres 16或更高版本
如果输入可能不是有效的整数文字,请使用专用函数
pg_input_is_valid()
进行测试,而不会引发异常:型
SQL
CASE
在没有ELSE
的情况下默认为null
-按默认升序排序最后。请参阅:新的内置功能比手动解决方案更快,更可靠。不同之处还在于它不尝试修复损坏的输入。
Postgres 15岁以上
要在强制转换之前删除非数字字符,从而避免可能的异常,请执行以下操作:
型
regexp_replace()
表达式有效地删除了所有非数字,因此只保留数字或空字符串。(见下文。)\D
是字符类[^[:digit:]]
的简写,表示所有非数字([^0-9]
)。在旧的Postgres版本中,设置为过时的
standard_conforming_strings = off
,你必须使用Posix转义字符串语法E'\\D'
来转义反斜杠\
。这是Postgres 8.3的默认设置,所以你需要为你的过时版本。g
用于 “globally”,指示替换 * 所有 * 次,而不仅仅是第一次。-
)。integer
无效。使用NULLIF
将空字符串转换为NULL
。(您可以考虑使用0
。)保证结果有效。这个过程是针对问题主体中要求的**
integer
**的转换,而不是标题中提到的numeric
*。如何快速?
一种方式是index on an expression。
型
然后在
ORDER BY
子句中使用相同的表达式:型
使用
EXPLAIN ANALYZE
测试函数索引是否被实际使用。tjrkku2a2#
另外,如果你想按一个文本列排序,该列有可转换为float的内容,那么这就可以做到:
字符串