From: gzh <gzhcoder@126.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
Date: Fri, 28 Apr 2023 11:43:16 +0800 (CST)
Message-ID: <207c2ff5.342d.187c5f53ea3.Coremail.gzhcoder@126.com> (raw)
In-Reply-To: <1109902.1682604682@sss.pgh.pa.us>
References: <328d2a0.828a.18799afd0ac.Coremail.gzhcoder@126.com>
<1597875806.606392.1681915893771@office.mailbox.org>
<70c95d95.7a58.1879a77ed4f.Coremail.gzhcoder@126.com>
<2993958.1681924695@sss.pgh.pa.us>
<5027d9b9.7aad.187b831fb18.Coremail.gzhcoder@126.com>
<1073908593.624606.1682427189969@office.mailbox.org>
<7480a292.7dc7.187c271a743.Coremail.gzhcoder@126.com>
<1109902.1682604682@sss.pgh.pa.us>
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gzhcoder@126.com, tgl@sss.pgh.pa.us, ewie@ewie.name
Subject: Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
In-Reply-To: <207c2ff5.342d.187c5f53ea3.Coremail.gzhcoder@126.com>
References: <328d2a0.828a.18799afd0ac.Coremail.gzhcoder@126.com> <1597875806.606392.1681915893771@office.mailbox.org> <70c95d95.7a58.1879a77ed4f.Coremail.gzhcoder@126.com> <2993958.1681924695@sss.pgh.pa.us> <5027d9b9.7aad.187b831fb18.Coremail.gzhcoder@126.com> <1073908593.624606.1682427189969@office.mailbox.org> <7480a292.7dc7.187c271a743.Coremail.gzhcoder@126.com> <1109902.1682604682@sss.pgh.pa.us> <207c2ff5.342d.187c5f53ea3.Coremail.gzhcoder@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox