Recognizing invalid dates in postgresql
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.
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 a开发者_JAVA技巧ccept, and use that in a sub-select, but that seems awfully inelegant.)
Cheers,
Jurgen
http://www.tek-tips.com/viewthread.cfm?qid=1280050&page=9
A more generic approach than the above:
create function safe_cast(text,anyelement)
returns anyelement
language plpgsql as $$
begin
$0 := $1;
return $0;
exception when others then
return $2;
end; $$;
Used like this:
select safe_cast('Jan 10, 2009', '2011-01-01'::timestamp)
select safe_cast('Jan 10, 2009', null::timestamp)
Credited to the friendly dudes at the #postgresql irc channel. :)
You could write a pgsql function with an exception handling block.
精彩评论