Recognizing invalid dates in postgresql

Question

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 accept, and use that in a sub-select, but that seems awfully inelegant.)

Cheers,

Jurgen

a date field cannot contain invalid dates. Do you perhaps mean a varchar column that contains date values? — user330315
– user330315, Commented Jul 18, 2011 at 8:07
It is, indeed, a varchar column which I try to interpret as a date. — Jurgen Pletinckx
– Jurgen Pletinckx, Commented Jul 18, 2011 at 9:35

Gaslight Deceive Subvert · Accepted Answer · 2011-07-18 10:23:25Z

11

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. :)

edited Jul 18, 2011 at 10:23

answered Jul 18, 2011 at 8:37

Gaslight Deceive Subvert

20.7k20 gold badges94 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jurgen Pletinckx Over a year ago

That seems to be exactly what I need. Thanks!

MarHoff Over a year ago

Solid answer, would love to see an lightweight solution implemented in PostgreSQL core. As mentioned by this comment multiplying transactions with proposed answer might cause performance issue in some cases. stackoverflow.com/questions/25374707/…

Denis de Bernardy · Accepted Answer · 2011-07-18 08:30:52Z

0

You could write a pgsql function with an exception handling block.

answered Jul 18, 2011 at 8:30

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

Recognizing invalid dates in postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related