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.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
5 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
Kiran NaiduCarmeloTO_DATE function between PostgreSQL 8.2 and 8.3 « DbRunas – Noticias y Recursos sobre Bases de Datosjlamkyong Recent comment authors
newest oldest most voted
software development company
Guest
software development company

Interesting,

I have question if you know other programming languages is it faster yo use a store procedure or just getting the data a nd filtering it with a programming language

Anyway, thanks for the post

Windows tips
Guest
Windows tips

Does this tutorial still work on the latest PostgreSQL version???

jla
Guest
jla

It looks like you are using to_date where to_char would be more appropriate: to_date(text,text) – convert string to date to_char(timestamp, text) – convert time stamp to string Instead of relying on the old implicit cast of timestamp to text ISO 8601 format, having to_date parse it according to you ‘YYYY-MM-DD’ format, convert it into a date object and then spit it out as ISO 8601 text (effectively truncating all but the date part of the timestamp), you could use to_char: SELECT to_char(createddate,’YYYY-MM-DD’) FROM n_url_test; Since to_char expects a timestamp there should not be a casting error and you aren’t turning… Read more »

Kiran Naidu
Guest
Kiran Naidu

how to use between with two database columns i.e(restrictions.between(fromDate,”startDate”,”endDate”);

trackback
TO_DATE function between PostgreSQL 8.2 and 8.3 « DbRunas – Noticias y Recursos sobre Bases de Datos

[…] http://www.mkyong.com/database/to_date-function-between-postgresql-82-and-83/ June 27, 2011   //   PostgreSQL   //   No Comments   //   […]