Чем заменить number в postgresql

Convert the NUMBER data type from Oracle to PostgreSQL – Part 1

An Oracle to PostgreSQL migration in the AWS Cloud can be a multistage process with different technologies and skills involved, starting from the assessment stage to the cutover stage. For more information about the migration process, see Database Migration—What Do You Need to Know Before You Start? and the following posts on best practices, including the migration process and infrastructure considerations, source database considerations, and target database considerations for the PostgreSQL environment.

In the migration process, data type conversion from Oracle to PostgreSQL is one of the key stages. Multiple tools are available on the market, such as AWS Schema Conversion Tool (AWS SCT) and Ora2PG, which help you with data type mapping and conversion. However, it’s always recommended that you do an upfront analysis on the source data to determine the target data type, especially when working with data types like NUMBER.

Many tools often convert this data type to NUMERIC in PostgreSQL. Although it looks easy to convert Oracle NUMBER to PostgreSQL NUMERIC, it’s not ideal for performance because calculations on NUMERIC are very slow when compared to an integer type. Unless you have a large value without scale that can’t be stored in BIGINT, you don’t need to choose the column data type NUMERIC. And as long as the column data doesn’t have float values, you don’t need to define the columns as the DOUBLE PRECISION data type. These columns can be INT or BIGINT based on the values stored. Therefore, it’s worth the effort to do an upfront analysis to determine if you should convert the Oracle NUMBER data type to INT, BIGINT, DOUBLE PRECISION, or NUMERIC when migrating to PostgreSQL.

This series is divided into two posts. In this post, we cover two analysis methods to define the target data type column in PostgreSQL depending on how the NUMBER data type is defined in the Oracle database and what values are stored in the columns’ data. In the second post, we cover how to change the data types in the target PostgreSQL database after analysis using the AWS SCT features and map data type using transformation.

Before we suggest which of these data types in PostgreSQL is a suitable match for Oracle NUMBER, it’s important to know the key differences between these INT, BIGINT, DOUBLE PRECISION, and NUMERIC.

Comparing numeric types in PostgreSQL

One of the important differences between INT and BIGINT vs. NUMERIC is storage format. INT and BIGINT have a fixed length: INT has 4 bytes, and BIGINT has 8 bytes. However, the NUMERIC type is of variable length and stores 0–255 bytes as needed. So NUMERIC needs more space to store the data. Let’s look at the following example code (we’re using PostgreSQL 12), which shows the size differences of tables with integer columns and NUMERIC:

From the preceding output, INT occupies 3,515 bytes and NUMERIC occupies 6,152 bytes for the same set of values. However, you see the same table size because PostgreSQL is designed such that its own internal natural alignment is 8 bytes, meaning consecutive fixed-length columns of differing size must be padded with empty bytes in some cases. We can see that with the following code:

From the preceding output, it’s clear that an empty PostgreSQL row requires 24 bytes of various header elements, a SMALLINT is 2 bytes, and a BIGINT is 8 bytes. However, combining a SMALLINT and BIGINT takes 16 bytes. This is because PostgreSQL is padding the smaller column to match the size of the following column for alignment purposes. Instead of 2 + 8 = 10, the size becomes 8 + 8 = 16.

Integer and floating point values representation and their arithmetic operations are implemented differently. In general, floating point arithmetic is more complex than integer arithmetic. It requires more time for calculations on numeric or floating point values as compared to the integer types.

PostgreSQL BIGINT and INT data types store their own range of values. The BIGINT range (8 bytes) is -9223372036854775808 to 9223372036854775807 :

The INT range (4 bytes) is -2147483648 to 2147483647 :

For information about these data types, see Numeric Types.

With the NUMBER data type having precision and scale in Oracle, it can be either DOUBLE PRECISION or NUMERIC in PostgreSQL. One of the main differences between DOUBLE PRECISION and NUMERIC is storage format and total length of precision or scale supported. DOUBLE PRECISION is fixed at 8 bytes of storage and 15-decimal digit precision.

DOUBLE PRECISION offers a similar benefit as compared to the NUMERIC data type as explained for BIGINT and INT in PostgreSQL.

With DOUBLE PRECISION, because its limit is 15 decimals, for any data with higher precision or scale we might be affected by data truncation during data migration from Oracle. See the following code:

When considering data type in PostgreSQL when the NUMBER data type has decimal information, we should check for max precision along with max scale and decide accordingly if the target data type is either DOUBLE PRECISION or NUMERIC.

If you’re planning to store values that require a certain precision or arithmetic accuracy, the DOUBLE PRECISION data type may be the right choice for your needs. For example, if you try to store the result of 2/3, there is some rounding when the 15th digit is reached when you use DOUBLE PRECISION. It’s used for not only rounding the value based on precision limit, but also for the arithmetic accuracy. If you consider the following example, a double precision gives the wrong answer.

Starting with PostgreSQL 12, performance has been improved by using a new algorithm for output of real and double precision values. In previous versions (older than PostgreSQL 12), displayed floating point values were rounded to 6 (for real) or 15 (for double precision) digits by default, adjusted by the value of the parameter extra_float_digits . Now, whenever extra_float_digits is more than zero (as it is by default from PostgreSQL 12 and newer), only the minimum number of digits required to preserve the exact binary value are output. The behavior is the same as before when extra_float_digits is set to zero or less.

Oracle NUMBER and PostgreSQL NUMERIC

In Oracle, the NUMBER data type is defined as NUMBER(precision, scale) and in PostgreSQL, NUMERIC is defined as NUMERIC(precision, scale), with precision and scale defined as follows:

• Precision – Total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point
• Scale – Count of decimal digits in the fractional part, to the right of the decimal point

Analysis before data type conversion

Data type changes may have some side effects, which we discuss in this post. Before converting data types, you need to check with your service or application team if they have the following:

• Any dependency of these data types (INT and BIGINT) in application SQLs that require type casting
• Any change required in stored procedures or triggers for these data types
• Any dependency in Hibernate-generated code
• If these data type changes have any impact on future data growth
• Any plans in the future for these columns to store any fractional values

Without proper analysis and information on the application code and the database code objects, it’s not recommended to change the data types. If you do so, your application queries may start showing performance issues because they require a type casting internally from NUMERIC to INT or BIGINT. If you have code objects like procedures or triggers with these data types’ dependency, you need to make changes to those objects to avoid performance issues. Future data growth may impact these conversions as well. If you convert it to INT based on the current data, however, it may go beyond INT value in the future. Make sure you don’t have any plans to store fractional values after migration is complete. If that is the requirement, you need to choose a different data type than INT or BIGINT.

For this post, we examine two different methods to analyze the information to recommend INT, BIGINT, DOUBLE PRECISION, or NUMERIC:

• Metadata-based data type conversion
• Actual data-based data type conversion

Analysis method: Metadata-based data type conversion

By looking at the metadata information of Oracle tables with columns of the NUMBER or INTEGER data type, we can come up with the target data type recommendations.

Conversion to PostgreSQL INT or BIGINT

You can covert Oracle tables with columns of the data type NUMBER or INTEGER to PostgreSQL INT or BIGINT if they meet the following criteria:

• The data type is NUMERIC or INTEGER in Oracle
• DATA_PRECISION is NOT NULL (not a variable length NUMERIC)
• DATA_SCALE is 0 (integers and not float values)
• MAX_LENGTH is defined as <=18 (DATA_PRECISION <=18)
• If DATA_PRECISION is < 10, use INT. If DATA_PRECISION is 10–18, use BIGINT.`

You can use the following query on Oracle to find candidate columns of NUMERIC or INTEGER data type that can be converted to BIGINT or INT in PostgreSQL:

The following code is an example output of the preceding query, which shows precision and scale of NUMBER data types for a particular user:

The output shows four columns ( REGION_ID of the COUNTRIES table, CUSTOMER_ID of the CUSTOMER table, and ORDER_ID and VALUE of the ORDERS table) that have precision between 10–18, which can be converted to BIGINT in PostgreSQL. The remaining columns can be converted to INT.

Conversion to PostgreSQL DOUBLE PRECISION or NUMERIC

You can convert Oracle tables with columns of the data type NUMBER or INTEGER to PostgreSQL DOUBLE PRECISION or NUMERIC if they meet the following criteria:

• The data type is NUMERIC or NUMBER in Oracle
• DATA_PRECISION is NOT NULL
• DATA_SCALE is > 0 (float values)

If DATA_PRECISION + DATA_SCALE <= 15, choose DOUBLE PRECISION. If DATA_PRECISION + DATA_SCALE > 15, choose NUMERIC.

You can use the following query on Oracle to find candidate columns of NUMERIC or INTEGER data type that can be converted to DOUBLE PRECISION or NUMERIC in PostgreSQL:

The following is an example output of the query:

The output contains the column COMMISSION_PCT from the EMPLOYEES table, which can be converted as DOUBLE PRECISION. The output also contains two columns ( LONGI and LATTI ) that can be NUMERIC because they have (precision + scale) > 15.

Changing the data type for all candidate columns may not have the same impact or performance gain. Most columns that are part of the key or index appear in joining conditions or in lookup, so changing the data type for those columns may have a big impact. The following are guidelines for converting the column data type in PostgreSQL, in order of priority:

• Consider changing the data type for columns that are part of a key (primary, unique, or reference)
• Consider changing the data type for columns that are part of an index
• Consider changing the data type for all candidate columns

Analysis method: Actual data-based data type conversion

In Oracle, the NUMBER data type is often defined with no scale, and those columns are used for storing integer value only. But because scale isn’t defined, the metadata doesn’t show if these columns store only integer values. In this case, we need to perform a full table scan for those columns to identify if the columns can be converted to BIGINT or INT in PostgreSQL.

A NUMERIC column may be defined with DATA_PRECISION higher than 18, but all column values fit well in the BIGINT or INT range of PostgreSQL.

You can use the following SQL code in an Oracle database to find if the actual DATA_PRECISION and DATA_SCALE is in use. This code performs a full table scan, so you need to do it in batches for some tables, and if possible run it during low peak hours in an active standby database (ADG):

Based on the result of the preceding code, use the following transform rules:

• If MAX_DATA_PRECISION < 10 and MAX_DATA_SCALE = 0, convert to INT
• If DATA_PRECISION is 10–18 and MAX_DATA_SCALE = 0, convert to BIGINT
• if MAX_DATA_SCALE > 0 and MAX_DATA_PRECISION + MAX_DATA_SCALE <= 15, convert to DOUBLE PRECISION
• if MAX_DATA_SCALE > 0 and MAX_DATA_PRECISION + MAX_DATA_SCALE > 15, convert to NUMERIC

Let’s look at the following example. In Oracle, create the table number_col_test with four columns defined and a couple of rows inserted:

We next run a query to look at the data to find the actual precision and scale. From the following example, can_be_int has precision as 6 and scale as 0 even though it’s defined as NUMBER(8,4). So we can define this column as INT in PostgreSQL.

In the following output, can_be_bigint has precision as 16 and scale as 0 even though it’s defined as NUMBER(19,2). So we can define this column as BIGINT in PostgreSQL.

In the following output, can_be_doubleprecision has precision as 8 and scale as 2 even though it’s defined as NUMBER(15,2). We can define this column as DOUBLE PRECISION in PostgreSQL.

In the following output, should_be_numeric has precision as 10 and scale as 9 even though it’s defined as NUMBER(20,10). We can define this column as NUMERIC in PostgreSQL.

Summary

Converting the NUMBER data type from Oracle to PostgreSQL is always tricky. It’s not recommended to convert all NUMBER data type columns to NUMERIC or DOUBLE PRECISION in PostgreSQL without a proper analysis of the source data. Having the appropriate data types helps improve performance. It pays long-term dividends by spending time upfront to determine the right data type for application performance. From the Oracle system tables, you get precision and scale of all the columns of all the tables for a user. With this metadata information, you can choose the target data type in PostgreSQL as INT, BIGINT, DOUBLE PRECISION, or NUMERIC.

However, you can’t always depend on this information. Although this metadata shows the scale of some columns as >0, the actual data might not have floating values. Look at the actual data in every table’s NUMBER columns (which are scale >0), and decide on the target columns’ data types.

Precision(m)	Scale(n)	Oracle	PostgreSQL
<= 9	0	NUMBER(m,n)	INT
9 > m <=18	0	NUMBER(m,n)	BIGINT
m+n <= 15	n>0	NUMBER(m,n)	DOUBLE PRECISION
m+n > 15	n>0	NUMBER(m,n)	NUMERIC

In the next post in this series, we cover methods to convert the data type in PostgreSQL after analysis is complete, and we discuss data types for the source Oracle NUMBER data type.

About the authors

Baji Shaik is a Consultant with AWS ProServe, GCC India. His background spans a wide depth and breadth of expertise and experience in SQL/NoSQL database technologies. He is a Database Migration Expert and has developed many successful database solutions addressing challenging business requirements for moving databases from on-premises to Amazon RDS and Aurora PostgreSQL / MySQL . He is an eminent author, having written several books on PostgreSQL . A few of his recent works include “ PostgreSQL Configuration“, “Beginning PostgreSQL on the Cloud”, and “ PostgreSQL Development Essentials“. Furthermore, he has delivered several conference and workshop sessions.

Sudip Acharya is a Sr. Consultant with the AWS ProServe team in India. He works with internal and external Amazon customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Deepak Mahto is a Consultant with the AWS Proserve Team in India. He has been working as Database Migration Lead, helping and enabling customers to migrate from commercial engines to Amazon RDS.His passion is automation and has designed and implemented multiple database or migration related tools.

ORACLE NUMBER(*,0) equivalent in POSTGRESQL

I have a table in ORACLE with a column which has a data type of NUMBER(*,0) . As per my understanding it means precision will be 38 since that is the maximum limit of precision and scale value will be 0 which means no numbers will be allowed after decimal point i.e. will store integers?

I have a requirement where I have to create same table in PostgreSQL. Is it possible to write NUMERIC(*,0) in PostgreSQL? If not, what will be PostgreSQL equivalent of NUMBER(*,0 )?

Чем заменить number в postgresql

Table of Contents

PostgreSQL has a rich set of native data types available to users. Users can add new types to PostgreSQL using the CREATE TYPE command.

Table 8.1 shows all the built-in general-purpose data types. Most of the alternative names listed in the “ Aliases ” column are the names used internally by PostgreSQL for historical reasons. In addition, some internally used or deprecated types are available, but are not listed here.

Table 8.1. Data Types

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit [ ( n ) ]	fixed-length bit string
bit varying [ ( n ) ]	varbit [ ( n ) ]	variable-length bit string
boolean	bool	logical Boolean (true/false)
box	rectangular box on a plane
bytea	binary data ( “ byte array ” )
character [ ( n ) ]	char [ ( n ) ]	fixed-length character string
character varying [ ( n ) ]	varchar [ ( n ) ]	variable-length character string
cidr	IPv4 or IPv6 network address
circle	circle on a plane
date	calendar date (year, month, day)
double precision	float8	double precision floating-point number (8 bytes)
inet	IPv4 or IPv6 host address
integer	int , int4	signed four-byte integer
interval [ fields ] [ ( p ) ]	time span
json	textual JSON data
jsonb	binary JSON data, decomposed
line	infinite line on a plane
lseg	line segment on a plane
macaddr	MAC (Media Access Control) address
macaddr8	MAC (Media Access Control) address (EUI-64 format)
money	currency amount
numeric [ ( p , s ) ]	decimal [ ( p , s ) ]	exact numeric of selectable precision
path	geometric path on a plane
pg_lsn	PostgreSQL Log Sequence Number
pg_snapshot	user-level transaction ID snapshot
point	geometric point on a plane
polygon	closed geometric path on a plane
real	float4	single precision floating-point number (4 bytes)
smallint	int2	signed two-byte integer
smallserial	serial2	autoincrementing two-byte integer
serial	serial4	autoincrementing four-byte integer
text	variable-length character string
time [ ( p ) ] [ without time zone ]	time of day (no time zone)
time [ ( p ) ] with time zone	timetz	time of day, including time zone
timestamp [ ( p ) ] [ without time zone ]	date and time (no time zone)
timestamp [ ( p ) ] with time zone	timestamptz	date and time, including time zone
tsquery	text search query
tsvector	text search document
txid_snapshot	user-level transaction ID snapshot (deprecated; see pg_snapshot )
uuid	universally unique identifier
xml	XML data

Compatibility

Each data type has an external representation determined by its input and output functions. Many of the built-in types have obvious external formats. However, several types are either unique to PostgreSQL , such as geometric paths, or have several possible formats, such as the date and time types. Some of the input and output functions are not invertible, i.e., the result of an output function might lose accuracy when compared to the original input.

Prev	Up	Next
7.8. WITH Queries (Common Table Expressions)	Home	8.1. Numeric Types

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Чем заменить number в postgresql

PostgreSQL предоставляет пользователям богатый ассортимент встроенных типов данных. Кроме того, пользователи могут создавать свои типы в PostgreSQL , используя команду CREATE TYPE .

Таблица 8.1 содержит все встроенные типы данных общего пользования. Многие из альтернативных имён, приведённых в столбце « Псевдонимы » , используются внутри PostgreSQL по историческим причинам. В этот список не включены некоторые устаревшие типы и типы для внутреннего применения.

Таблица 8.1. Типы данных

Имя	Псевдонимы	Описание
bigint	int8	знаковое целое из 8 байт
bigserial	serial8	восьмибайтное целое с автоувеличением
bit [ ( n ) ]	битовая строка фиксированной длины
bit varying [ ( n ) ]	varbit [ ( n ) ]	битовая строка переменной длины
boolean	bool	логическое значение (true/false)
box	прямоугольник в плоскости
bytea	двоичные данные ( « массив байт » )
character [ ( n ) ]	char [ ( n ) ]	символьная строка фиксированной длины
character varying [ ( n ) ]	varchar [ ( n ) ]	символьная строка переменной длины
cidr	сетевой адрес IPv4 или IPv6
circle	круг в плоскости
date	календарная дата (год, месяц, день)
double precision	float8	число двойной точности с плавающей точкой (8 байт)
inet	адрес узла IPv4 или IPv6
integer	int , int4	знаковое четырёхбайтное целое
interval [ поля ] [ ( p ) ]	интервал времени
json	текстовые данные JSON
jsonb	двоичные данные JSON, разобранные
line	прямая в плоскости
lseg	отрезок в плоскости
macaddr	MAC-адрес
macaddr8	адрес MAC (Media Access Control) (в формате EUI-64)
money	денежная сумма
numeric [ ( p , s ) ]	decimal [ ( p , s ) ]	вещественное число заданной точности
path	геометрический путь в плоскости
pg_lsn	последовательный номер в журнале PostgreSQL
pg_snapshot	снимок идентификатора транзакций
point	геометрическая точка в плоскости
polygon	замкнутый геометрический путь в плоскости
real	float4	число одинарной точности с плавающей точкой (4 байта)
smallint	int2	знаковое двухбайтное целое
smallserial	serial2	двухбайтное целое с автоувеличением
serial	serial4	четырёхбайтное целое с автоувеличением
text	символьная строка переменной длины
time [ ( p ) ] [ without time zone ]	время суток (без часового пояса)
time [ ( p ) ] with time zone	timetz	время суток с учётом часового пояса
timestamp [ ( p ) ] [ without time zone ]	дата и время (без часового пояса)
timestamp [ ( p ) ] with time zone	timestamptz	дата и время с учётом часового пояса
tsquery	запрос текстового поиска
tsvector	документ для текстового поиска
txid_snapshot	снимок идентификаторов транзакций для пользовательского уровня (устаревший тип; см. pg_snapshot )
uuid	универсальный уникальный идентификатор
xml	XML-данные

Совместимость

Каждый тип данных имеет внутреннее представление, скрытое функциями ввода и вывода. При этом многие встроенные типы стандартны и имеют очевидные внешние форматы. Однако есть типы, уникальные для PostgreSQL , например геометрические пути, и есть типы, которые могут иметь разные форматы, например, дата и время. Некоторые функции ввода и вывода не являются в точности обратными друг к другу, то есть результат функции вывода может не совпадать со входным значением из-за потери точности.