Updated on 2024-10-14 GMT+08:00

Character Types

Table 1 lists the character data types supported by GaussDB. For string operators and related built-in functions, see Character Processing Functions and Operators.

Table 1 Character types

Name

Description

Storage Space

CHAR(n)

CHARACTER(n)

NCHAR(n)

Fixed-length character string, blank padded. n indicates the string length. If it is not specified, the default precision 1 is used.

The maximum value of n is 10485760 (10 MB).

VARCHAR(n)

CHARACTER VARYING(n)

Variable-length string. In PostgreSQL-compatible mode, n indicates the string length. In other compatibility modes, n indicates the byte length.

The maximum value of n is 10485760 (10 MB).

If n is not contained, the maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum length (of a int, b varchar) is 1 GB – 85 – 4 = 1,073,741,735.

VARCHAR2(n)

Variable-length string. It is an alias for VARCHAR(n) type, compatible with Oracle. n indicates the string length.

The maximum value of n is 10485760 (10 MB).

If n is not contained, the maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum length (of a int, b varchar) is 1 GB – 85 – 4 = 1,073,741,735.

NVARCHAR2(n)

Variable-length string. n indicates the string length.

The maximum value of n is 10485760 (10 MB).

If n is not contained, the maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum length (of a int, b varchar) is 1 GB – 85 – 4 = 1,073,741,735.

CLOB

Big text object. It is compatible with the Oracle database.

The maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum length (of a int, b varchar) is 1 GB – 85 – 4 = 1,073,741,735.

TEXT

Variable-length string.

The maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum length (of a int, b varchar) is 1 GB – 85 – 4 = 1,073,741,735.

  1. In addition to the size limitation on each column, the total size of each tuple is 1,073,741,739 bytes (1 GB – 85 bytes).
  2. NCHAR is the alias of the bpchar type, and VARCHAR2(n) is the alias of the VARCHAR(n) type.

GaussDB has two other fixed-length character types, as shown in Table 2. The name type exists only for the storage of identifiers in the internal system catalogs and is not intended for use by general users. Its length is currently defined as 64 bytes (63 usable characters plus terminator). The type "char" only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

Table 2 Special character types

Name

Description

Storage Space

name

Internal type for object names

64 bytes

"char"

Single-byte internal type

1 byte

Examples

-- Create a table.
openGauss=# CREATE TABLE char_type_t1 
(
    CT_COL1 CHARACTER(4)
)DISTRIBUTE BY HASH (CT_COL1);

-- Insert data.
openGauss=# INSERT INTO char_type_t1 VALUES ('ok');

-- Query data in the table.
openGauss=# SELECT ct_col1, char_length(ct_col1) FROM char_type_t1;
 ct_col1 | char_length 
---------+-------------
 ok      |           4
(1 row)

-- Delete the table.
openGauss=# DROP TABLE char_type_t1;
 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
-- Create a table.
openGauss=# CREATE TABLE char_type_t2  
(
    CT_COL1 VARCHAR(5)
)DISTRIBUTE BY HASH (CT_COL1);

-- Insert data.
openGauss=# INSERT INTO char_type_t2 VALUES ('ok');

openGauss=# INSERT INTO char_type_t2 VALUES ('good');

-- Specify the type length. An error is reported if an inserted string exceeds this length.
openGauss=# INSERT INTO char_type_t2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
CONTEXT:  referenced column: ct_col1

-- Specify the type length. A string exceeding this length is truncated.
openGauss=# INSERT INTO char_type_t2 VALUES ('too long'::varchar(5));

-- Query data.
openGauss=# SELECT ct_col1, char_length(ct_col1) FROM char_type_t2;
 ct_col1 | char_length 
---------+-------------
 ok      |           2
 good    |           4
 too l   |           5
(3 rows)

-- Delete data.
openGauss=# DROP TABLE char_type_t2;