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

Unsigned Integer Types

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

Table 1 Unsigned Integer Types

Name

Description

Storage Space

Range

TINYINT UNSIGNED [ZEROFILL]

Unsigned tiny integer.

1 byte

[0, 255]

SMALLINT UNSIGNED [ZEROFILL]

Unsigned small integer.

2 bytes

[0, 65,535]

MEDIUMINT UNSIGNED [ZEROFILL]

Unsigned medium-sized integer.

4 bytes

[0, 16,777,215]

INT/INTEGER UNSIGNED [ZEROFILL]

Unsigned common integer.

4 bytes

[0, 4,294,967,295]

BIGINT UNSIGNED [ZEROFILL]

Unsigned large integer.

8 bytes

[0, 18,446,744,073,709,551,615]
  • If the value of sql_mode contains strict_trans_tables and a negative value is input in the unsigned integer column, an ERROR message is reported.
  • If the value of sql_mode contains strict_trans_tables and non-numeric strings are input in the unsigned integer column, an ERROR message is reported.
  • If the value of sql_mode does not contain strict_trans_tables and a negative value is input in the unsigned integer column, a WARNING message is reported and 0 is returned.
  • If the value of sql_mode does not contain strict_trans_tables and non-numeric strings are input in the unsigned integer column, the truncated value is returned.
  • The ZEROFILL attribute can be set for the unsigned 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) UNSIGNED ZEROFILL, a value of 5 is retrieved as 0005.
    • 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
 
-- Create a table whose column is of unsigned integer type.
m_db=# CREATE TABLE test_uint
(
    a TINYINT UNSIGNED, 
    b SMALLINT UNSIGNED,
    c MEDIUMINT UNSIGNED,
    d INTEGER UNSIGNED,
    e BIGINT UNSIGNED
);

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

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

-- With the strict mode enabled, an error is reported if a negative value 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_uint VALUES (-1, -1, -1, -1, -1);
ERROR:  unsigned tinyint out of range
CONTEXT:  referenced column: a

-- With the strict mode disabled, a warning message is reported and 0 is returned if a negative value is inserted.
m_db=# SET SQL_MODE = '';
SET
m_db=# INSERT INTO test_uint VALUES (-1, -1, -1, -1, -1);
WARNING:  unsigned tinyint out of range
CONTEXT:  referenced column: a
INSERT 0 1
m_db=# select * from test_uint;
 a | b | c | d | e
---+---+---+---+---
 0 | 0 | 0 | 0 | 0
(1 row)

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