红移数据库中的数据值"0"格式无效错误

Question

我们遇到了一个奇怪的问题，与我们的查询有关。以下是我们运行的查询：

INSERT into test 
SELECT 
    member.name as mem_name, 
    CASE WHEN ( member.dob>0 AND length (member.dob)=8 ) THEN (DATEDIFF(year,to_date("dob",'YYYYMMDD'), to_date(20140716,'YYYYMMDD'))) WHEN ( member.dob=0 ) Then 0 END As Age, 
    20140716021501 
FROM 
    member 

以下是我们表格中的示例数据。

|name           |dob
|Ajitsh         |0        |
|rk             |51015    |
|s_thiagarajan  |19500130 |
|madhav_7       |19700725 |
|1922           |0        |
|rekha          |25478    |
|vmkurup        |0        |
|ravikris       |19620109 |
|ksairaman      |0        |
|sruthi         |0        |
|rrbha          |19630825 |
|sunilsw        |0        |
|sunilh         |0        |
|venky_pmv      |19701207 |
|malagi         |0        |
|an752001       |0        |
|edsdf          |19790201 |
|anuanand       |19730724 |
|fresh          |19720821 |
|ampharcopharma |19590127 |
|Nanze          |19621123 |

出生日期以bigint格式存储，格式为YYYYMMDD。 在数据中有一些行，其中日期无效，例如0、51015等。 在某些情况下，此查询会引发以下错误。

INSERT INTO test not successful
An error occurred when executing the SQL command:
INSERT into test 
SELECT 
    member.name as mem_name, 
    CASE WHEN ( member.dob>0 AND length (member.dob)=8 ) THEN (DATEDIFF(y...

ERROR: Data value "0" has invalid format
    Detail: 
    -----------------------------------------------
    error:  Data value "0" has invalid format
    code:      1009
    context:   PG ERROR
    query:     92776
    location:  pg_utils.cpp:2731
    process:   query1_30 [pid=1434]
    -----------------------------------------------


Execution time: 3.99s

1 statement failed.

但奇怪的是，它会随机出现错误，而不是每次都出现。很多时候，即使查询或数据集没有任何改变，它也能正常工作。有时候，第二次或第三次尝试也可以正常工作。我怀疑是 to_date 函数导致了这个错误。但为什么会随机出错，而不是每次运行都会出错呢？

为了支持我的假设，我还尝试了这个小查询。

SELECT to_date(20140716,'YYYYMMDD'), to_date(0,'YYYYMMDD');

但这也会创建相同的情况。它会随机出现错误，而其他时间则顺利运行。

- jimy

---

你有没有得到这个问题的答案？我有一个相关的问题。我正在执行一个 WHERE (getdate()::date - date_value::date) <=90 操作，但返回的错误是“数据值....格式无效”。奇怪的是，getdate()::date - date_value::date 的使用效果如预期，但在 WHERE 中使用时会抛出错误。 - Moon_Watcher

@Moon_Watcher，我对这个问题没有得到令人满意的答案。我认为你可以使用“WITH”子句来解决这个问题。例如：WITH temp AS (SELECT getdate()::date - date_value::date as diff FROM tablename) SELECT columns FROM temp WHERE diff <=90。 - jimy

Answer 1

如果可以忽略这种类型的值，只需将其转换为日期格式，您可以按照以下方式操作。

SELECT to_date('20140716','YYYYMMDD'), to_date('0','FMYYYYMMDD');

在这里，FM抑制了前导零和尾随空格，否则会添加以使模式的输出成为固定宽度。

- Ilesh Patel

---

@llesh 感谢您提供 FM 选项。但我的主要关注点是为什么错误会随机出现，而不是每次查询都会触发。 - jimy