pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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


view thread (11+ messages)

Message-ID: <207c2ff5.342d.187c5f53ea3.Coremail.gzhcoder@126.com>
Permalink:  ../207c2ff5.342d.187c5f53ea3.Coremail.gzhcoder@126.com/
Also on:    postgresql.org/message-id/207c2ff5.342d.187c5f53ea3.Coremail.gzhcoder@126.com

 · 

reply

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