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

Signed Integer Types

Table 1 lists the signed integer types supported by M-compatible databases.

Table 1 Signed Integer Types

Name

Description

Storage Space

Range

TINYINT [SIGNED] [ZEROFILL]

Tiny integer. The type alias is INT1.

1 byte

[-128, 127]

SMALLINT [SIGNED] [ZEROFILL]

Small integer. The type alias is INT2.

2 bytes

[-32768, 32767]

MEDIUMINT [SIGNED] [ZEROFILL]

Medium-sized integer.

4 bytes

[-8388608, 8388607]

INT/INTEGER [SIGNED] [ZEROFILL]

Common integer. The type alias is INT4.

4 bytes

[-2147483648, 2147483647]

BIGINT [SIGNED] [ZEROFILL]

Large integer. The type alias is INT8.

8 bytes

[-2^63, 2^63-1]
  • If the value of sql_mode contains strict_trans_tables and non-numeric strings are input in the signed integer column, an ERROR message is reported.
  • If the value of sql_mode does not contain strict_trans_tables and non-numeric strings are input in the signed integer column, the truncated value is returned.
  • To create a table column, adding SIGNED syntax after the integer type is equivalent to directly using the signed integer type.
  • The ZEROFILL attribute can be set for the signed integer type.
    • When the ZEROFILL attribute is set, zeros are added before the value based on the display width. If the display width is not set, the default display width of the data type is used. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.
    • When the ZEROFILL attribute is set, the UNSIGNED attribute is automatically added. Even if the ZEROFILL attribute is set to SIGNED, the UNSIGNED attribute is converted to the UNSIGNED attribute.

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
69
70
71
72
73
74
75
76
77
78
 
-- Create a table whose column is of signed integer type.
m_db=# CREATE TABLE test_int
(
    a TINYINT, 
    b SMALLINT,
    c MEDIUMINT,
    d INTEGER,
    e BIGINT
);

-- Insert data.
m_db=# INSERT INTO test_int VALUES (100, 200, 300, 400, 500);

-- Query data in the table.
m_db=# SELECT * FROM test_int;
  a  |  b  |  c  |  d  |  e
-----+-----+-----+-----+-----
 100 | 200 | 300 | 400 | 500
(1 row)

-- With the strict mode enabled, an error is reported if a non-numeric string is inserted.
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 test_int VALUES ('1@2@3', '200&20&2', '4%4', '200$30', '1@3');
ERROR:  invalid input syntax for integer: "1@2@3"
LINE 1: INSERT INTO test_int VALUES ('1@2@3', '200&20&2', '4%4', '20...
                                     ^
CONTEXT:  referenced column: a

-- With the strict mode disabled, a warning message is reported if a non-numeric string is inserted, and the string is automatically truncated.
m_db=# SET SQL_MODE = '';
SET
m_db=# INSERT INTO test_int VALUES ('1@2@3', '200&20&2', '4%4', '200$30', '1@3');
WARNING:  invalid input syntax for integer: "1@2@3"
LINE 1: INSERT INTO test_int VALUES ('1@2@3', '200&20&2', '4%4', '20...
                                     ^
CONTEXT:  referenced column: a
WARNING:  invalid input syntax for integer: "200&20&2"
LINE 1: INSERT INTO test_int VALUES ('1@2@3', '200&20&2', '4%4', '20...
                                              ^
CONTEXT:  referenced column: b
WARNING:  invalid input syntax for integer: "4%4"
LINE 1: INSERT INTO test_int VALUES ('1@2@3', '200&20&2', '4%4', '20...
                                                          ^
CONTEXT:  referenced column: c
WARNING:  invalid input syntax for integer: "200$30"
LINE 1: ...INTO test_int VALUES ('1@2@3', '200&20&2', '4%4', '200$30', ...
                                                             ^
CONTEXT:  referenced column: d
WARNING:  invalid input syntax for integer: "1@3"
LINE 1: ...st_int VALUES ('1@2@3', '200&20&2', '4%4', '200$30', '1@3');
                                                                ^
CONTEXT:  referenced column: e
INSERT 0 1

m_db=# SELECT * FROM test_int;
  a  |  b  |  c  |  d  |  e
-----+-----+-----+-----+-----
 100 | 200 | 300 | 400 | 500
 1   | 200 | 4   | 200 |   1
(2 rows)

-- Set the zerofill attribute.
m_db=# dROP TABLE IF EXISTS t1;
DROP TABLE
m_db=# CREATE TABLE t1 (a int 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 test_int;
Parent topic: Numeric Types