postgresql - 错误 : invalid input syntax for type date: ""

Question

我有以下 psql 查询，无法理解为什么会出现错误 错误:类型日期的输入语法无效:“”。

我的查询如下所示:

SELECT count(*) FROM campaigns 
WHERE 
    dstart >= '2010-09-02' AND 
    dend <= '2010-09-02' AND 
    status != 'S' AND 
    status != 'C' AND 
    status != 'E' AND 
    (dsignoff <> '' AND dsignoff is not null) AND 
    (dstart <> '' AND dstart is not null) AND 
    (dend <> '' AND dend is not null) AND 
    clientid=20005294;

dstart、dend和dsignoff都定义为日期类型。

Answer 1

由于dstart,dend 和dsignoff 被定义为日期，它们不能与表示无效日期('') 的字符串进行比较。试试这个:

SELECT count(*) FROM campaigns 
WHERE 
    dstart >= '2010-09-02' AND 
    dend <= '2010-09-02' AND 
    status != 'S' AND 
    status != 'C' AND 
    status != 'E' AND 
    (dsignoff is not null) AND 
    (dstart is not null) AND 
    (dend is not null) AND 
    clientid=20005294;