在 WHERE 子句中将 VARCHAR 转换为 INT/NUMBER

Question

我在PostgreSQL DB中ACTIVITIES_T了一个表。ACTIVITY_CODE是VARCHAR列：

ID (INT)  |  ACTIVITY_CODE (VARCHAR) | ACTIVITY_TEXT (VARCHAR)
-------------------------------------------------------------
..            ..                      ..
7             10012                   ..
8             10013                   ..
9             10014                   ..
10            10015                   ..
11            SPECIAL                 ..

我有一个连续的伪数块，10012..10015，我需要用BETWEEN a AND b运算符或>= n1 and <= n2来选择。

但并非ACTIVITY_CODE中的所有条目都是数值。
我试过这个：

select * from activities_t  where activity_code::int >= 10012 and 
activity_code::int <= 10015;

但得到错误

ERROR:  invalid input syntax for integer: SPECIAL

Answer 1

SELECT *
FROM   activities_t
WHERE  CASE WHEN activity_code ~ '^d+$'  -- only digits
THEN activity_code::int BETWEEN 10012 AND 10015 END;

CASE包装器避免了异常。仅考虑仅由数字组成的字符串。(所以也没有空白填充！
不需要ELSECASE因为默认为NULL，并且只有TRUE符合WHERE子句的条件。

当然，这不能在activity_code上使用任何普通索引。

如果需要更好的性能，请清理表设计，或者，如果无法选择，请创建表达式索引：

CREATE INDEX foo_idx ON activities_t((CASE WHEN activity_code ~ '^d+$'
THEN activity_code::int END));

并匹配查询中的表达式：

SELECT *, CASE WHEN activity_code ~ '^d+$' THEN activity_code::int END
FROM   activities_t
WHERE  CASE WHEN activity_code ~ '^d+$'
THEN activity_code::int END BETWEEN 10012 AND 10015;

这与第一个查询略有不同，如果它不会触发索引扫描，则成本会略高。

Answer 2

补充一下Brandstetter @Erwin写的内容，如果在 Spring Data 中使用它，就会有人抱怨使用@Query的::运算符。

若要解决此问题，请转义::，如本例所示， https://stackoverflow.com/a/43982706/1005607

\:\:

或者修改查询以使用cast函数。

@Query(value="select * from activities_t where " + 
"   case when  activity_code ~ '^d+$' " +  // only digits   
"   then cast(activity_code as int) between 10012 and 10015 end ", 
nativeQuery=true)
public List<ActivityT> getByActivityCodes();