SQL Redshift 上的无效数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49372415/
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
Invalid digits on Redshift
提问by Maurício Borges
I'm trying to load some data from stage to relational environment and something is happening I can't figure out.
我正在尝试将一些数据从阶段加载到关系环境中，但发生了一些我无法弄清楚的事情。
I'm trying to run the following query:
我正在尝试运行以下查询：
SELECT
CAST(SPLIT_PART(some_field,'_',2) AS BIGINT) cmt_par
FROM
public.some_table;
The some_field is a column that has data with two numbers joined by an underscore like this:
some_field 是一列，其中包含两个数字并用下划线连接的数据，如下所示：
some_field -> 38972691802309_48937927428392
And I'm trying to get the second part.
我正在尝试获得第二部分。
That said, here is the error I'm getting:
也就是说，这是我得到的错误：
[Amazon](500310) Invalid operation: Invalid digit, Value '1', Pos 0,
Type: Long
Details:
-----------------------------------------------
error: Invalid digit, Value '1', Pos 0, Type: Long
code: 1207
context:
query: 1097254
location: :0
process: query0_99 [pid=0]
-----------------------------------------------;
Execution time: 2.61s
Statement 1 of 1 finished
1 statement failed.
It's literally saying some numbers are not valid digits. I've already tried to get the exactly data which is throwing the error and it appears to be a normal field like I was expecting. It happens even if I throw out NULL fields.
从字面上看，有些数字不是有效数字。我已经尝试获取引发错误的确切数据，它似乎是我所期望的正常字段。即使我抛出 NULL 字段，它也会发生。
I thought it would be an encoding error, but I've not found any references to solve that. Anyone has any idea?
我认为这将是一个编码错误，但我没有找到任何参考来解决这个问题。任何人有任何想法？
Thanks everybody.
谢谢大家。
回答by szeitlin
I just ran into this problem and did some digging. Seems like the error Value '1'is the misleading part, and the problem is actually that these fields are just not valid as numeric.
我刚刚遇到了这个问题并做了一些挖掘。似乎错误Value '1'是误导部分，问题实际上是这些字段作为数字无效。
In my case they were empty strings. I found the solution to my problem in this blogpost, which is essentially to find any fields that aren't numeric, and fill them with null before casting.
在我的情况下，它们是空字符串。我在这篇博文中找到了我的问题的解决方案，它本质上是找到任何不是数字的字段，并在转换之前用 null 填充它们。
select cast(colname as integer) from
(select
case when colname ~ '^[0-9]+$' then colname
else null
end as colname
from tablename);
Bottom line: this Redshift error is completely confusing and really needs to be fixed.
底线：这个 Redshift 错误完全令人困惑，确实需要修复。
回答by Jaliya Sumanadasa
After casting ?the error got disappear - CAST(COLUMN1 as char(xx)) =CAST(COLUMN2 as char(xxx))
铸造后？错误消失了 - CAST(COLUMN1 as char(xx)) =CAST(COLUMN2 as char(xxx))
回答by Gordon Linoff
Hmmm. I would start by investigating the problem. Are there any non-digit characters?
嗯。我将从调查问题开始。有没有非数字字符？
SELECT some_field
FROM public.some_table
WHERE SPLIT_PART(some_field, '_', 2) ~ '[^0-9]';
Is the value too long for a bigint?
a 的值是否太长bigint？
SELECT some_field
FROM public.some_table
WHERE LEN(SPLIT_PART(some_field, '_', 2)) > 27
If you need more than 27 digits of precision, consider a decimalrather than bigint.
如果您需要超过 27 位的精度，请考虑使用 adecimal而不是bigint。
