Postgres-如果无法强制转换，则将列强制转换为类型或Null

Question

我从postgres中的mongodb集合加载了一个postgres表。尽管postgres列的类型是"bigint"，但有些行比最大bigint大，所以当我尝试从该表更新另一个表时，它会出错。还有一些带有非法字符的bigint列，例如我通过清除的"_2131441">

WHERE col_name !~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$';

如何根据列的类型强制转换整列为有效列，如果不是这样，则将其设置为null/none？

Answer 1

使用CASE表达式：

CASE WHEN col_name !~ '^(+|-)?[[:digit:]]+$'
THEN NULL::bigint
WHEN col_name::numeric NOT BETWEEN -9223372036854775808 AND 9223372036854775807
THEN NULL::bigint
ELSE col_name::bigint
END

请注意，bigint是一个整数，不允许使用十进制分隔符。