TO_DATE function between PostgreSQL 8.2 and 8.3

As we all know PostgreSQL 8.3 make very strong checking on data type, it make a lot application hit many data type error after migration from PostgreSQL8.x to PostgreSQL8.3. Mostly is cause by data type checking.

One of the common error is to_date() function. It accept two text as parameters.

to_date(text,text)

Ok now i create a table as following. A simple table call n_url_test contain a createddate as timestamp data type.

CREATE TABLE n_url_test
(
  urltestid bigserial NOT NULL,
  createddate timestamp without time zone DEFAULT now(),
  CONSTRAINT n_url_to_test_pkey PRIMARY KEY (urltestid)
)

I run following sql in PostgreSQL8.2, it return my expected result as ‘YYYY-MM-DD’ format.

--PostgreSQL 8.2
---------------
select  to_date(createddate,'YYYY-MM-DD') from n_url_test;

However after i migrated to PostgreSQL8.3, i hit following error

--PostgreSQL 8.3
---------------
select  to_date(createddate,'YYYY-MM-DD') from n_url_test;

--------------------------------------------------------------------------------
ERROR:  function to_date(timestamp without time zone, unknown) does not exist
LINE 1: select  to_date(createddate,'YYYY-MM-DD') from n_url_test
                ^
HINT:  No function matches the given name and argument types. 
You might need to add explicit type casts.

********** Error **********

PostgreSQL 8.3 will not automatically convert from timestamp to text for us like what it does in previous version. We need to explicit type casts createddate like following

--PostgreSQL 8.3
---------------
select  to_date(createddate::text,'YYYY-MM-DD') from n_url_test;

Append ::text to createddate will explicit convert datatype as text, It’s work. PostgreSQL8.3 strictly checking on data type is good function , but please do not forget there still have a lot people using previous version. It cause a lot problem if application is design base on ignore data type checking like what i mention above.