FAQ
While writing a date validation function, I'm not sure which condition
to check for in the following exception handler.





CREATE OR REPLACE FUNCTION IsValidDateTime(timestamp with time zone)
RETURNS BOOLEAN AS $$

BEGIN

PERFORM CAST($1 AS TIMESTAMP WITH TIME ZONE);

RETURN TRUE;

EXCEPTION WHEN INVALID_DATETIME_FORMAT THEN

RETURN FALSE;

END

$$ LANGUAGE 'plpgsql';





If I call it this way: SELECT IsValidDateTime('2008-05-06 00:00:95');




I would expect it to return FALSE but it returns:

ERROR: date/time field value out of range: "2008-05-06 00:00:95"



Is there either a specific error code to use or is there a generic catch
all code? I didn't see anything better listed at
http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html



Thanks.

Search Discussions

  • Tom Lane at May 6, 2008 at 2:45 pm

    "Wright, George" <George.Wright@infimatic.com> writes:
    I would expect it to return FALSE but it returns:
    ERROR: date/time field value out of range: "2008-05-06 00:00:95"
    Is there either a specific error code to use or is there a generic catch
    all code? I didn't see anything better listed at
    http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html
    Well, the way to debug this sort of thing is to look at the error code
    number. In psql you do it like this:

    regression=# \set VERBOSITY verbose
    regression=# select '2008-05-06 00:00:95'::timestamptz;
    ERROR: 22008: date/time field value out of range: "2008-05-06 00:00:95"
    LOCATION: DateTimeParseError, datetime.c:3112

    and then a search in the appendix for 22008 finds

    22008 DATETIME FIELD OVERFLOW

    regards, tom lane
  • Wright, George at May 6, 2008 at 2:52 pm
    Perfect! Thank you.

    -----Original Message-----
    From: Tom Lane
    Sent: Tuesday, May 06, 2008 10:45 AM
    To: Wright, George
    Cc: pgsql-novice@postgresql.org
    Subject: Re: [NOVICE] function exception

    "Wright, George" <George.Wright@infimatic.com> writes:
    I would expect it to return FALSE but it returns:
    ERROR: date/time field value out of range: "2008-05-06 00:00:95"
    Is there either a specific error code to use or is there a generic catch
    all code? I didn't see anything better listed at
    http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html
    Well, the way to debug this sort of thing is to look at the error code
    number. In psql you do it like this:

    regression=# \set VERBOSITY verbose
    regression=# select '2008-05-06 00:00:95'::timestamptz;
    ERROR: 22008: date/time field value out of range: "2008-05-06 00:00:95"
    LOCATION: DateTimeParseError, datetime.c:3112

    and then a search in the appendix for 22008 finds

    22008 DATETIME FIELD OVERFLOW

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 6, '08 at 2:36p
activeMay 6, '08 at 2:52p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Wright, George: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase