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.