Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

>If you're porting a bunch of code written for Oracle to Postgres,

>you'd have to run around and change every occurrence of "date" to

>"timestamp" ... unless you install orafce, in which case you can

>rely on this alias type that orafce creates. But you do then have

>two types named "date" in the system, so you have to be careful

>about search_path settings or you'll get more confusion than it's

>worth.

When I put the oracle schema in the search_path behind the pg_catalog schema,

even if I don't make any changes to the to_date(text) functions of the public schema,

the application behaves the same as the old version of the database(PostgreSQL 12.10 and orafce 3.15).

Can I understand that when the pg_catalog schema is in front of the oracle schema,

the date type that does not specify the schema in all functions takes the date type

of pg_catalog.date first, so setting the search_path as below is also a solution.

postgres=# show search_path;

search_path

-------------------------------------

"$user", public, pg_catalog, oracle

At 2023-04-27 22:11:22, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>gzh <gzhcoder(at)126(dot)com> writes:
>> I did the following two tests and found that the return value of pg_catalog.date and oracle.date are inconsistent.
>
>Yeah ... that's pretty much the point. Oracle uses the name "date"
>for a data type that Postgres (and the SQL standard) calls a "timestamp".
>That's very ancient on their side and I doubt they'll ever change it.
>
>If you're porting a bunch of code written for Oracle to Postgres,
>you'd have to run around and change every occurrence of "date" to
>"timestamp" ... unless you install orafce, in which case you can
>rely on this alias type that orafce creates. But you do then have
>two types named "date" in the system, so you have to be careful
>about search_path settings or you'll get more confusion than it's
>worth.
>
> regards, tom lane