Re: function to_char(unknown) is not unique at character 8

Hi

čt 6. 7. 2023 v 11:19 odesílatel gzh <gzhcoder(at)126(dot)com> napsal:

> Hi,
>
>
> I upgraded the version of PostgreSQL from 12.6 to 12.13,
>
> when I execute the sql below , the to_char function caused the following
> error.
>
>
> ---------------SQL------------------
>
> select TO_CHAR('1000000');
>
>
> ERROR: function to_char(unknown) is not unique at character 8
>
> HINT: Could not choose a best candidate function. You might need to add
> explicit type casts.
>
>
> There is no problem before the upgrade and to_char function comes from the
> Orafce extension.
>
> The configuration of the old and new databases is as follows.
>
>
> Database server (old): PostgreSQL 12.6（orafce3.13）
>
> Database server (new): PostgreSQL 12.13（orafce3.24）
>
>
> The new database has successfully installed the orafce 3.24 extension.
>
> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
>
> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>
> so either the difference between 12.6 and 12.13 or the difference between
> orafce 3.13 and 3.24 is suspicious.
>
>
> What is the reason for the problem and how to fix the error?
>

The new orafce contains to_char for numeric types and to_char for
timestamp. Old orafce had to_char function (with one argument) only for
numeric types.

This is the reason why the system doesn't know if a string of unknown type
(postgresql string literal) should be processed as numeric or as a
timestamp.

The best fix is specify used type like

(2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('1000000'::int);
┌─────────┐
│ to_char │
╞═════════╡
│ 1000000 │
└─────────┘
(1 row)

Regards

Pavel

>
> Regards
>
>