识别postgresql中的无效日期 [英] Recognizing invalid dates in postgresql

问题描述

我正在尝试将脏输入解析到 postgres 表中.我有一个日期"字段的问题，偶尔包含非日期，例如00000000"或20100100".pg 拒绝接受这些，这是正确的.

I am trying to parse dirty input into postgres tables. I have a problem with a 'date' field occasionally containing non-dates such as '00000000' or '20100100'. pg refuses to accept these, and rightly so.

有没有办法让 postgres 识别无效日期(或者只识别有效日期，如果效果更好的话)，这样我就可以替换一个合理的默认值?

Is there a way to have postgres recognize invalid dates (or only valid dates, if that works better), so I can substitute a sensible default?

(我曾考虑建立一个表格，列出我愿意接受的日期，并在子选择中使用它，但这似乎非常不雅.)

(I've considered building a table listing the dates I'm willing to accept, and use that in a sub-select, but that seems awfully inelegant.)

干杯，

于尔根

推荐答案

http://www.tek-tips.com/viewthread.cfm?qid=1280050&page=9

比上述更通用的方法:

create function safe_cast(text,anyelement) 
returns anyelement 
language plpgsql as $$ 
begin 
    $0 := $1; 
    return $0; 
    exception when others then 
        return $2; 
end; $$;

像这样使用:

select safe_cast('Jan 10, 2009', '2011-01-01'::timestamp)
select safe_cast('Jan 10, 2009', null::timestamp)

感谢#postgresql irc 频道的友好人士.:)

Credited to the friendly dudes at the #postgresql irc channel. :)

这篇关于识别postgresql中的无效日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！