Numeric Types

Table 1 lists all available types. For arithmetic operators and related built-in functions, see Arithmetic Functions and Operators.

**Table 1** Integer types
Name	Description	Storage Space	Range
TINYINT [UNSIGNED]	Tiny integer. The signed alias is INT1, and the unsigned alias is UINT1.	1 byte	The signed value ranges from 0 to +255. The unsigned value ranges from 0 to +255. When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the signed value ranges from –128 to +127.
SMALLINT [UNSIGNED]	Small integer. The signed alias is INT2, and the unsigned alias is UINT2.	2 bytes	The signed value ranges from –32,768 to +32,767. The unsigned value ranges from 0 to +65,535.
MEDIUMINT [UNSIGNED]	Medium-sized integer. The signed alias is INT4, and the unsigned alias is UINT4.	4 bytes	The signed value ranges from –2147483648 to +2147483647. The unsigned value ranges from 0 to +4294967295.
INTEGER [UNSIGNED]	Common integer. The signed alias is INT4, and the unsigned alias is UINT4.	4 bytes	The signed value ranges from –2,147,483,648 to +2,147,483,647. The unsigned value ranges from 0 to +4,294,967,295.
BINARY_INTEGER	Common integer. The signed alias is INTEGER.	4 bytes	–2,147,483,648 to +2,147,483,647
BIGINT [UNSIGNED]	Large integer. The signed alias is INT8, and the unsigned alias is UINT8.	8 bytes	The signed value ranges from –9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. The unsigned value ranges from 0 to +18,446,744,073,709,551,615.
INT16 [SIGNED]	A 16-byte integer cannot be used to create tables.	16 bytes	–170,141,183,460,469,231,731,687,303,715,884,105,728 to +170,141,183,460,469,231,731,687,303,715,884,105,727

When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', integers support the display width and ZEROFILL attributes.
The display width does not limit the range of values that can be stored in a column, nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of –32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
When the display width is used in conjunction with the ZEROFILL attribute, zeros are padded in front of the numeric value to achieve the display width. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.
If you specify ZEROFILL for a numeric column, the UNSIGNED attribute is automatically added.
If ZEROFILL is not specified for a numeric column and only the width information is specified, the width information is not displayed in the table structure description.

Example:

-- Create a database.
gaussdb=# CREATE DATABASE b_database dbcompatibility = 'MYSQL';
gaussdb=# \c b_database

-- Create a table containing TINYINT data.
b_database=# CREATE TABLE int_type_t1
           (
            IT_COL1 TINYINT,
            IT_COL2 TINYINT UNSIGNED
           );

-- Insert data.
b_database=# INSERT INTO int_type_t1 VALUES(10,20);

-- View data.
b_database=# SELECT * FROM int_type_t1;
it_col1 | it_col2
--------+---------
     10 |      20
(1 row)

-- Drop the table.
b_database=# DROP TABLE int_type_t1;

   
    
      
      
        -- Create a table containing TINYINT, INTEGER, and BIGINT data.
b_database=# CREATE TABLE int_type_t2 
(
    a TINYINT, 
    b TINYINT,
    c INTEGER,
    d INTEGER UNSIGNED,
    e BIGINT,
    f BIGINT UNSIGNED
);

-- Insert data.
b_database=# INSERT INTO int_type_t2 VALUES(100, 10, 1000, 10000, 200, 2000);

-- View data.
b_database=# SELECT * FROM int_type_t2;
  a  | b  |  c   |   d    |  e  |  f
-----+----+------+--------+-----+------
 100 | 10 | 1000 |  10000 | 200 | 2000
(1 row)

-- Drop the table.
b_database=# DROP TABLE int_type_t2;

-- Delete the database.
b_database=# \c postgres
gaussdb=# DROP DATABASE b_database;

       

     

   
  

Numbers of the TINYINT, SMALLINT, INTEGER, BIGINT, or INT16 type, that is, integers can be stored. Saving a number with a decimal in any of the data types will result in errors.
If UNSIGNED is specified, negative values are not allowed.
The INTEGER type is the common choice, as it offers the best balance between range, storage size, and performance. Generally, use the SMALLINT type only if you are sure that the value range is within the SMALLINT value range. The storage speed of INTEGER is much faster. BIGINT is used only when the range of INTEGER is not large enough.
The unsigned numeric type can be used only in the row storage engine when sql_compatibility is set to 'MYSQL'.
When a minus sign, plus sign, or multiplication sign is used between integer values (one of which is of the UNSIGNED type), the result is unsigned.
The return value of the addition (+), subtraction (–), and multiplication (x) operations of the INT1, UINT1, UINT2, UINT4, or UINT8 type can exceed the range of the corresponding type. The return value of the addition (+), subtraction (–), and multiplication (x) operations of the INT2, INT4, or INT8 type cannot exceed the range of the corresponding type.
The UNSIGNED type cannot be converted to the SET type. Do not calculate or compare the UNSIGNED type with the SET type.
When sql_compatibility is set to 'MYSQL', non-numeric characters are automatically truncated or the value 0 is returned.

**Table 2** Arbitrary precision types
Name	Description	Storage Space	Range
NUMERIC[(p[,s])], DECIMAL[(p[,s])]	The value range of p is [1,1000], and the value range of s is [0,p]. When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', if the precision and scale are not specified, the default precision p is 10 and the scale s is 0. If a numeric type is not attached with parentheses, it is set to numeric (10,0) only when being applied to table columns. In other scenarios, the numeric type is used based on the original range by default. NOTE: p indicates the total digits, and s indicates the decimal digit.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Up to 131,072 digits before the decimal point, and up to 16,383 digits after the decimal point when no precision is specified.
NUMBER[(p[,s])]	Alias of the NUMERIC type.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Up to 131,072 digits before the decimal point, and up to 16,383 digits after the decimal point when no precision is specified.

Example:

-- Create a table.
gaussdb=# CREATE TABLE decimal_type_t1 
(
    DT_COL1 DECIMAL(10,4)
);

-- Insert data.
gaussdb=# INSERT INTO decimal_type_t1 VALUES(123456.122331);

-- Query data in the table.
gaussdb=# SELECT * FROM decimal_type_t1;
   dt_col1   
-------------
 123456.1223
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE decimal_type_t1;

   
        -- Create a table.
gaussdb=# CREATE TABLE numeric_type_t1 
(
    NT_COL1 NUMERIC(10,4)
);

-- Insert data.
gaussdb=# INSERT INTO numeric_type_t1 VALUES(123456.12354);

-- Query data in the table.
gaussdb=# SELECT * FROM numeric_type_t1;
   nt_col1   
-------------
 123456.1235
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE numeric_type_t1;

Compared to the integer types, the arbitrary precision numbers require larger storage space and have lower storage efficiency, operation efficiency, and poorer compression ratio results. The INTEGER type is the common choice when number types are defined. Arbitrary precision numbers are used when numbers exceed the maximum range indicated by the integers.
When NUMERIC/DECIMAL is used for defining a column, you are advised to specify the precision (p) and scale (s) for the column.
When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the input and output range specifications of the NUMERIC[(p[,s])] and DECIMAL[(p[,s])] types are different.
- Input format: The value can be a number or a numeric string.
  - If the value of sql_mode contains strict_trans_tables, an error is reported if the input value is invalid or exceeds the range.
  - If the value of sql_mode does not contain strict_trans_tables and the input value is invalid or exceeds the range, a warning message is reported and 0 or the truncated value is returned.
- Output format: The output format of the DECIMAL[(p[,s])] type is different from that of the NUMERIC[(p[,s])] type when the precision and scale are not specified. If NUMERIC has no precision or scale, the decimal part is retained during type conversion. For details, see the output format in the scenario where no parameter is set. If DECIMAL has no precision or scale, the precision is 10 and the scale is 0 during type conversion.

**Table 3** Sequence integer
Name	Description	Storage Space	Range
SMALLSERIAL	Two-byte serial integer	2 bytes.	–32,768 to +32,767.
SERIAL	Four-byte serial integer	4 bytes.	–2,147,483,648 to +2,147,483,647.
BIGSERIAL	Eight-byte serial integer	8 bytes.	–9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

Example:

   
    
      
      
        -- Create a table.
gaussdb=# CREATE TABLE smallserial_type_tab(a SMALLSERIAL);

-- Insert data.
gaussdb=# INSERT INTO smallserial_type_tab VALUES(default);

-- Insert data again.
gaussdb=# INSERT INTO smallserial_type_tab VALUES(default);

-- View data.
gaussdb=# SELECT * FROM smallserial_type_tab;  
 a 
---
 1
 2
(2 rows)

-- Create a table.
gaussdb=# CREATE TABLE serial_type_tab(b SERIAL);

-- Insert data.
gaussdb=# INSERT INTO serial_type_tab VALUES(default);

-- Insert data again.
gaussdb=# INSERT INTO serial_type_tab VALUES(default);

-- View data.
gaussdb=# SELECT * FROM serial_type_tab; 
 b 
---
 1
 2
(2 rows)

-- Create a table.
gaussdb=# CREATE TABLE bigserial_type_tab(c BIGSERIAL);

-- Insert data.
gaussdb=# INSERT INTO bigserial_type_tab VALUES(default);

-- Insert data again.
gaussdb=# INSERT INTO bigserial_type_tab VALUES(default);

-- View data.
gaussdb=# SELECT * FROM bigserial_type_tab;
 c 
---
 1
 2
(2 rows)

-- Drop the table.
gaussdb=# DROP TABLE smallserial_type_tab;

gaussdb=# DROP TABLE serial_type_tab;

gaussdb=# DROP TABLE bigserial_type_tab;

       

     

   
  

SMALLSERIAL, SERIAL, and BIGSERIAL are not real types. They are concepts used for setting a unique identifier for a table. Therefore, an integer column is created and its default value is set to be read from a sequencer. A NOT NULL constraint is used to ensure NULL is not inserted. In most cases, you may also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted unexpectedly. Finally, the sequencer belongs to the column. When the column or table is deleted, the sequencer is deleted at the same time. Currently, the SERIAL column can be specified only when you create a table. You cannot add the SERIAL column in an existing table. In addition, SERIAL columns cannot be created in temporary tables because SERIAL is not a real type. Therefore, column types in temporary tables cannot be converted to SERIAL.

Table 4 shows the floating point type.

The REAL(p,s), DOUBLE, DOUBLE(p,s), and FLOAT(p,s) floating-point types can be used only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'. For the behavior of the REAL, FLOAT, and DEC[(p[,s])] data types when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', see Table 4.
In Table 4, p is the precision, indicating the minimum acceptable total number of integral places, and s indicates the decimal digit.

**Table 4** Floating point types
Name	Description	Storage Space	Range
REAL, FLOAT4	Single precision floating points, inexact The REAL data type is mapped to the double-precision floating-point number FLOAT8 when the scenario described in Description is met. For details about the application scenario, see FLOAT8.	4 bytes.	–3.402E+38 to +3.402E+38, 6-bit decimal digits.
REAL(p,s)	The value range of p is [1,1000], and the value range of s is [0,p]. If the precision and scale are not specified, the precision p is 10 and the scale s is 0 by default. This type maps to NUMERIC. For details about the application scenario, see NUMERIC.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Maximum 131,072 digits before the decimal point and 16,383 digits after the decimal point when the precision and scale are specified to the maximum.
DOUBLE PRECISION, FLOAT8	Double precision floating points, inexact	8 bytes.	–1.79E+308 to +1.79E+308, 15-bit decimal digits.
DOUBLE	Double precision floating points, inexact This type maps to double-precision floating-point number FLOAT8. For details about the application scenario, see FLOAT8.	8 bytes.	–1.79E+308 to +1.79E+308, 15-bit decimal digits.
DOUBLE(p,s)	The value range of p is [1,1000], and the value range of s is [0,p]. If the precision and scale are not specified, the precision p is 10 and the scale s is 0 by default. This type maps to NUMERIC. For details about the application scenario, see NUMERIC.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Maximum 131,072 digits before the decimal point and 16,383 digits after the decimal point when the precision and scale are specified to the maximum.
FLOAT[(p)]	Floating points, inexact The value range of precision (p) is [1,53]. The type is selected based on the precision p. When p is less than or equal to 24, the mapping type is REAL. When p is greater than 24 or not specified, the mapping type is DOUBLE PRECISION. In the scenario described in NOTE, if the precision p is less than or equal to 24, or it is not specified, the mapping type is FLOAT4. If p is greater than 24, the mapping type is DOUBLE PRECISION.	4 bytes or 8 bytes.	-
FLOAT(p,s)	The value range of p is [1,1000], and the value range of s is [0,p]. If the precision and scale are not specified, the precision p is 10 and the scale s is 0 by default. This type maps to NUMERIC. For details about the application scenario, see NUMERIC.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Maximum 131,072 digits before the decimal point and 16,383 digits after the decimal point when the precision and scale are specified to the maximum.
BINARY_DOUBLE	DOUBLE PRECISION alias, compatible with Oracle	8 bytes.	–1.79E+308 to +1.79E+308, 15-bit decimal digits.
DEC[(p[,s])]	The value range of p is [1,1000], and the value range of s is [0,p]. If the scenario in Note is met and the precision and scale are not specified, the precision p is 10 and the scale s is 0 by default. This type maps to NUMERIC. For details about the application scenario, see NUMERIC.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Maximum 131,072 digits before the decimal point and 16,383 digits after the decimal point when the precision and scale are specified to the maximum.
INTEGER[(p[,s])]	The value range of p is [1,1000], and the value range of s is [0,p]. If the precision and scale are not specified, the precision p is 10 and the scale s is 0 by default. If the precision and scale are not specified, this type is mapped to INTEGER. If the precision and scale are specified, this type is mapped to NUMERIC.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Maximum 131,072 digits before the decimal point and 16,383 digits after the decimal point when the precision and scale are specified to the maximum. If the precision and scale are not specified, the value ranges from –2,147,483,648 to +2,147,483,647.

For the precision of the floating-point type, only the number of precision bits can be ensured when the data is directly read. When distributed computing is involved, the computation is executed on each DN and is finally aggregated to a CN. Therefore, the error may be amplified as the number of compute nodes increases.

When sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the input and output ranges of the FLOAT[(p[,s])], DOUBLE[(p[,s])], DEC[(p[,s])] and REAL[(p[,s])] types are different.

Input format: The value can be a number or a numeric string.
- If the value of sql_mode contains strict_trans_tables, an error is reported if the input value is invalid or exceeds the range.
- If the value of sql_mode does not contain strict_trans_tables and the input value is invalid or exceeds the range, a warning message is reported and 0 or the truncated value is returned.
Output format: The output format of the DEC[(p[,s])] type is different from that of the mapping NUMERIC[(p[,s])] type when the precision and scale are not specified. If NUMERIC has no precision or scale, the decimal part is retained during type conversion. For details, see the output format in the scenario where no parameter is set. If DEC has no precision or scale, the precision is 10 and the scale is 0 during type conversion.

Example:

   
    
      
      
        -- Create a table.
gaussdb=# CREATE TABLE float_type_t2 
(
    FT_COL1 INTEGER,
    FT_COL2 FLOAT4,
    FT_COL3 FLOAT8,
    FT_COL4 FLOAT(3),
    FT_COL5 BINARY_DOUBLE,
    FT_COL6 DECIMAL(10,4),
    FT_COL7 INTEGER(6,3)
)DISTRIBUTE BY HASH ( ft_col1);

-- Insert data.
gaussdb=# INSERT INTO float_type_t2 VALUES(10,10.365456,123456.1234,10.3214, 321.321, 123.123654, 123.123654);

-- View data.
gaussdb=# SELECT * FROM float_type_t2 ;
 ft_col1 | ft_col2 |   ft_col3   | ft_col4 | ft_col5 | ft_col6  | ft_col7 
---------+---------+-------------+---------+---------+----------+---------
      10 | 10.3655 | 123456.1234 | 10.3214 | 321.321 | 123.1237 | 123.124
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE float_type_t2;

-- Example: REAL(p,s), FLOAT(p,s), DOUBLE, and DOUBLE(p,s).
gaussdb=# CREATE DATABASE gaussdb_m  WITH dbcompatibility  'MYSQL';
gaussdb=# \c gaussdb_m
-- Set compatible version control parameters.
gaussdb_m=# SET b_format_version='5.7';
gaussdb_m=# SET b_format_dev_version='s1';
-- Create a table.
gaussdb_m=# CREATE TABLE t1(a real(10,2), b float(10,2), c double, d double(10,2));
-- Insert data.
gaussdb_m=# INSERT INTO t1 VALUES (1000.12, 2000.23, 3000.34, 4000.45);
-- Query data in the table.
gaussdb_m=# SELECT * FROM t1;
    a    |    b    |    c    |    d
---------+---------+---------+---------
 1000.12 | 2000.23 | 3000.34 | 4000.45
(1 row)

-- Delete the table and database.
gaussdb_m=# DROP TABLE t1;
gaussdb_m=# \c postgres;
gaussdb=# DROP DATABASE gaussdb_m;

-- Reset parameters.
gaussdb=# RESET ALL;

       

     

   
  