>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@sss.pgh.pa.us> wrote:
>gzh <gzhcoder@126.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
