Help Center/ GaussDB/ M-Compatibility Developer Guide(Distributed)/ SQL Reference/ Data Types/ Numeric Types/ Fixed-Point Types
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Fixed-Point Types

The fixed-point types are used to store exact numeric values. Table 1 lists the fixed-point types supported by M-compatible databases.

Table 1 Fixed-Point Types

Name

Description

Storage Space

Value Range (Decimal Digits)

NUMERIC[(p[,s])] [ZEROFILL]
  • The value range of the precision p is [1,65], and the value range of the scale s is [0,30] (s ≤ p).
  • If the precision and scale are not specified, the precision p is 10 and the scale s is 0 by default.

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 81 digits

DECIMAL[(p[,s])] [ZEROFILL]

This type maps 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.

Up to 81 digits

DEC[(p[,s])] [ZEROFILL]

This type maps 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.

Up to 81 digits

FIXED[(p[,s])] [ZEROFILL]

This type maps 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.

Up to 81 digits
  • The precision p indicates the maximum number of digits, and the scale s indicates the number of decimal digits.
  • If the input value exceeds the range implied by the scale, a NOTICE message is reported and the truncated value is returned.
  • If the value of sql_mode contains strict_trans_tables, an ERROR message is reported if the input value is invalid or exceeds the range implied by the precision.
  • If the value of sql_mode does not contain strict_trans_tables and the input value is invalid or exceeds the range implied by the precision, the truncated value or the maximum value within the range implied by the precision is returned.
  • The ZEROFILL attribute of the fixed-point number type can be set.
    • When the ZEROFILL attribute is set, zeros are added to the integer and decimal places of a value based on the precision and scale. If the precision and scale are not set, the default precision and scale of the data type are used as the specific precision and scale. For example, for a column declared as NUMERIC(10,5) ZEROFILL, a value of 2.1 is retrieved as 00002.10000.
    • When the ZEROFILL attribute is set, the UNSIGNED attribute is automatically added.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
-- Create a table whose columns are of fixed-point type.
m_db=# CREATE TABLE num_tab1
(
    a NUMERIC(10,5), 
    b DECIMAL(10,5),
    c DEC(10,5),
    d FIXED(10,5)
);

-- Insert data within the range implied by the specified precision and scale.
m_db=# INSERT INTO num_tab1 VALUES(1234.56, 1234.56, 1234.56, 1234.56);

-- If the inserted data exceeds the range implied by the specified scale, a notice message is reported, and the value truncated based on the specified scale is returned.
m_db=# INSERT INTO num_tab1 VALUES(12.3456789, 12.3456789, 12.3456789, 12.3456789);
NOTICE:  Truncated incorrect DECIMAL value: '12.3456789'
CONTEXT:  referenced column: a
INSERT 0 1

-- Query data in the table.
m_db=# SELECT * FROM num_tab1;
     a      |     b      |     c      |     d
------------+------------+------------+------------
   12.34568 |   12.34568 |   12.34568 |   12.34568
 1234.56000 | 1234.56000 | 1234.56000 | 1234.56000
(2 rows)

-- With the strict mode enabled, an error is reported if the inserted data exceeds the range implied by the specified precision.
m_db=# SET SQL_MODE = 'strict_trans_tables,only_full_group_by,no_zero_in_date,no_zero_date,error_for_division_by_zero,
no_auto_create_user,no_engine_substitution';
SET
m_db=# INSERT INTO num_tab1 VALUES(12345678999, 12345678999, 12345678999, 12345678999);
ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 5 must round to an absolute value less than 10^5.
CONTEXT:  referenced column: a

-- With the strict mode disabled, if the inserted data exceeds the range implied by the specified precision, a warning message is reported and the maximum value within the range implied by the specified precision is returned.
m_db=# SET SQL_MODE = '';
SET
m_db=# INSERT INTO num_tab1 VALUES(12345678999, 12345678999, 12345678999, 12345678999);
WARNING:  numeric field overflow
DETAIL:  A field with precision 10, scale 5 must round to an absolute value less than 10^5.
CONTEXT:  referenced column: a
INSERT 0 1

-- Query data in the table.
m_db=# SELECT * FROM num_tab1;
      a      |      b      |      c      |      d
-------------+-------------+-------------+-------------
  1234.56000 |  1234.56000 |  1234.56000 |  1234.56000
    12.34568 |    12.34568 |    12.34568 |    12.34568
  99999.99999 | 99999.99999 | 99999.99999 | 99999.99999
(3 rows)

-- Set the zerofill attribute.
m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
m_db=# CREATE TABLE t1 (a numeric zerofill);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(1);
INSERT 0 1
m_db=# SELECT * FROM t1;
     a      
------------
 0000000001
(1 row)

-- Drop the table.
m_db=# DROP TABLE num_tab2;
Parent topic: Numeric Types