Forum OpenACS Q&A: Re: to_date vs. to_timestamp

Posted by Jarkko Laine on
Now I understand why you guys seem to have been online 24h last couple of days :)

I've also in a middle of a porting effort, so here comes a few more issues which might or might not be more obvious than the one Lars posted.

  • coalesce() in PG is not the same as decode() in Oracle. This should be obvious if one had read their docs, but still I found some code where they had been treated like they were 1-to-1. coalesce(x,y,z) selects first of its arguments that is not NULL and therefore corresponds (mostly) to nvl(x,y) in Oracle. nvl can only take two arguments, coalesce as many as you want. In contrast, decode corresponds to normal (CASE WHEN ... THEN ... ELSE) syntax in PostgreSQL (and SQL92). If you port decode function to a coalesce function, you won't get an error but a possibly really hard-to-find misbehaviour from your program.
  • trunc(datevalue) doesn't work in PG. Instead, you have to use date_trunc and there the accuracy of truncation comes before the source (like date_trunc('date','2003-07-18 04:00 PM')), not after it like in oracle syntax. You also have to state the accuracy, you can't leave it out and get default behaviour like in Oracle (which is day, btw). This is, however, easy to find out since you'll get an error if you do something wrong.
I think I had something else in my mind, too, but I forgot it already :) Maybe I'll get back to this later.