Support for NUMBER datatype

[Thomas Morgan]

Here is a conversation I started in Stackoverflow:

We are in the process of evaluating a migration from Oracle Enterprise Edition to Postgresql. One of the most used datatypes we use is NUMBER (unrestricted - No precision/No scale).

I am looking at orafce and I do not see an implementation of NUMBER even though I see implementations for DATE and VARCHAR2.

Does anyone know if there is a reason why NUMBER was not implemented in the extension?

Does anyone know if is possible to redefine using a Domain? I tried the following and basic testing gave good results:

CREATE DOMAIN NUMBER AS BIGINT;

CREATE TABLE A_NUMBER_TABLE(A_NUMBER NUMBER);

template1=# INSERT INTO A_NUMBER_TABLE VALUES(12222222222222222); INSERT 0 1

I got what I expected, but I am not sure it is good enough.

This seems too simple. Does anyone have any input on this topic?

Thanks in advance, Thomas

[Thomas Morgan]

I added that whole conversation to illustrate my problem, but my main question to this groups is "Can NUMBER be built into Orafce or was there a reason why it never was"?

Thanks

[Pavel Stehule]

Hi

Dne středa 3. ledna 2024 v 17:36:16 UTC+1 uživatel thomasmorg...@gmail.com napsal:

Here is a conversation I started in Stackoverflow:

We are in the process of evaluating a migration from Oracle Enterprise Edition to Postgresql. One of the most used datatypes we use is NUMBER (unrestricted - No precision/No scale).

I am looking at orafce and I do not see an implementation of NUMBER even though I see implementations for DATE and VARCHAR2.

Does anyone know if there is a reason why NUMBER was not implemented in the extension?

Date and varchar2 are +/- aliases - oracle.date is timestamp, and varchar2 is varchar. Number from Oracle cannot be implemented just as alias - it can be int, bigint or numeric. Isn't possible to write PostgreSQL type with this very different behave without hacking Postgres. Using numeric that has "unlimited" precision and scale is not solution, because the numeric type is not good for using as primary keys (id).  So usually this type is not emulated and it is replaced by native PostgreSQL type. Ora2pg supports this replacement.

 

Does anyone know if is possible to redefine using a Domain? I tried the following and basic testing gave good results:

CREATE DOMAIN NUMBER AS BIGINT;

CREATE TABLE A_NUMBER_TABLE(A_NUMBER NUMBER);

template1=# INSERT INTO A_NUMBER_TABLE VALUES(12222222222222222); INSERT 0 1

I got what I expected, but I am not sure it is good enough.

This seems too simple. Does anyone have any input on this topic?

if you need it like this, then all should to work

Regards

Pavel

 

Thanks in advance, Thomas

[Thomas Morgan]

Thanks Pavel

[Thomas Morgan]

Sorry, this is off-topic, but I was wondering if anybody know if there is a google group for asking general Postgresql questions?

[Christophe Pettus]

> On Jan 8, 2024, at 10:31, Thomas Morgan <thomasmorg...@gmail.com> wrote:
>
> Sorry, this is off-topic, but I was wondering if anybody know if there is a google group for asking general Postgresql questions?

The pgsql-general list is what you want:

https://lists.postgresql.org