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.
how to use between with two database columns i.e(restrictions.between(fromDate,”startDate”,”endDate”);
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 it into a date object. It goes directly to text using the formatting string of your choosing.
Does this tutorial still work on the latest PostgreSQL version???
you can try, it should work
Can you tell me how to fix this error.
Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: operator does not exist: date ~~ unknown LINE 1: …tment_table.id_pat=638 and treatment_table.datein like ‘2012… ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in C:\wamp\www\gmc\datemenu.php on line 695
Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\gmc\datemenu.php on line 697
Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\gmc\datemenu.php on line 699
695 $ress= pg_exec($conn,$query);
696
697 $max=pg_num_rows($ress);
698
699 for($j=0; $j<pg_num_rows($ress); $j++){
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