Re: check date validity

Jeff Eckermann wrote:
>>>I need this function :
>>>CheckDate('2002-02-29') return false
>>>CheckDate('2002-02-28') return true
>>
>>Why would you want to do that? Just try to insert
>>'2002-02-29' into
>>your DATE column, and PostgreSQL will complain.
>
> That will cause the whole transaction to abort, which
> is probably not what is wanted.
>
> I don't know any way around this in Postgres. Best to
> check this in application code.

You could give this a try:
http://www.joeconway.com/str_validate.tar.gz

Drop in the contrib directory of a postgres source tree, untar, and then
make and install like any other contrib.

Here's some info from the README:
==================================
str_valid(text, oid) - returns true or false

Synopsis

str_valid(<string> text, <type_oid> oid)

Inputs

string
The string representing the value to be cast to a given data type

type_oid
The oid of the type to which <string> should be castable

Note: it may be convenient to use the form 'typename'::regtype to
represent the type oid.

Outputs

Returns 't' (true) if the cast will succeed, 'f' (false) if it will fail

Limitations

Currently the only supported data types are:
- date
- timestamp
- interval

Example usage

regression=# select str_valid('yesterday','timestamp'::regtype);
str_valid
-----------
t
(1 row)

regression=# select str_valid('next month','interval'::regtype);
str_valid
-----------
f
(1 row)

===================

HTH,

Joe