>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@xxxxxxxxxxxxx> wrote: >gzh <gzhcoder@xxxxxxx> 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
