Re: Please help!

From: "Vijayan" <vijayan(at)oyeindia(dot)com>

> I have a registration database. I need to know number of
> registrations / month in a report format. How will I get a report like
> this ?
>
> In Oracle I can give the query like this:-
> "select to_char(reg_date, 'Mon yyyy'), count(*) from
> register group by to_char(reg_date, 'Mon yyyy') order by
> to_date(to_char(reg_date, 'Mon yyyy'), 'Mon yyyy');"
>
> When I tried in psql, it gave this error. "No such function
> 'to_char' with the specified attributes". But in postgre there is a
> function called to_char(). Then why am I getting this error? My postgre
> version is "PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc
> egcs-2.91.66".

richardh=> select to_char(now(), 'Mon YYYY');
to_char
----------
Jul 2001
(1 row)

richardh=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.1.1 on i586-pc-linux-gnu, compiled by GCC 2.96
(1 row)

Try "\df to_char" to see how/if to_char is defined in 6.5.3 - if you have
one with timestamp,text try something like:

to_char(reg_date, 'Mon YYYY'::text)

and see if that helps. Perhaps have a look at the changes list (in the
administrators manual) and see if there's been any work on it.

Failing that, an upgrade might be in order.

- Richard Huxton