Raise error when date is not valid

Question

What I'm trying to do is to raise out of range error in case of dates outside of the supported range like what typecasting does.

I'm using PostgreSQL-9.1.6 on CentOS. The issue is below...

postgres=# select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

But the output I want to see is:

postgres=# select '20130229'::date;
ERROR:  date/time field value out of range: "20130229"

Surfing the web I found an informative page. So I did adding IS_VALID_JULIAN to the function body of to_date, adding the four lines marked + below to formatting.c:

Datum
to_date(PG_FUNCTION_ARGS)
{
    text       *date_txt = PG_GETARG_TEXT_P(0);
    text       *fmt = PG_GETARG_TEXT_P(1);
    DateADT         result;
    struct pg_tm tm;
    fsec_t          fsec;

    do_to_timestamp(date_txt, fmt, &tm, &fsec);

+       if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("date out of range: \"%s\"",text_to_cstring(date_txt))));

   result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;

   PG_RETURN_DATEADT(result);
}

Then I rebuilt PostgreSQL:

pg_ctl -m fast stop                       # 1. stopping pgsql
vi src/backend/utils/adt/formatting.c     # 2. using the version above
rm -rf /usr/local/pgsql/*                 # 3. getting rid of all bin files
./configure --prefix=/usr/local/pgsql 
  --enable-nls --with-perl --with-libxml
  --with-pam --with-openssl
make && make install                      # 4. rebuilding source    
pg_ctl start                              # 5. starting the engine

My bin directory info is below.

[/home/postgres]echo $PATH
/usr/lib64/qt-3.3/bin:
/usr/local/bin:
/bin:
/usr/bin:
/usr/local/sbin:
/usr/sbin:
/sbin:
/home/postgres/bin:
/usr/bin:
/usr/local/pgsql/bin:
/usr/local/pgpool/bin:
/usr/local/pgtop/bin/pg_top:

[/home/postgres]which pg_ctl
/usr/local/pgsql/bin/pg_ctl

[/home/postgres]which postgres
/usr/local/pgsql/bin/postgres

[/usr/local/bin]which psql
/usr/local/pgsql/bin/psql

But upon checking to_date again, the result remained the same.

postgres=# select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

Is there anything I missed?

Answer 1

You can write your own to_date() function, but you have to call it with its schema-qualified name. (I used the schema "public", but there's nothing special about that.)

create or replace function public.to_date(any_date text, format_string text)
returns date as
$$
select to_date((any_date::date)::text, format_string);
$$
language sql

Using the bare function name executes the native to_date() function.

select to_date('20130229', 'yyyymmdd');
2013-03-01

Using the schema-qualified name executes the user-defined function.

select public.to_date('20130229', 'yyyymmdd');
ERROR: date/time field value out of range: "20130229"
SQL state: 22008

I know that's not quite what you're looking for. But . . .

• It's simpler than rebuilding PostgreSQL from source.
• Fixing up your existing SQL and PLPGSQL source code is a simple search-and-replace with a streaming editor. I'm pretty sure that can't go wrong, as long as you really want every use of the native to_date() to be public.to_date().
• The native to_date() function will still work as designed. Extensions and other code might rely on its somewhat peculiar behavior. Think hard and long before you change the behavior of native functions.

New SQL and PLPGSQL would need to be reviewed, though. I wouldn't expect developers to remember to write public.to_date() every time. If you use version control, you might be able to write a precommit hook to make sure only public.to_date() is used.

The native to_date() function has behavior I don't see documented. Not only can you call it with February 29, you can call it with February 345, or February 9999.

select to_date('201302345', 'yyyymmdd');
2014-01-11

select to_date('2013029999', 'yyyymmdd');
2040-06-17