Character Types

Table 1 lists the character types that can be used in GaussDB(DWS). 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 string, blank padded. n indicates the string length. If it is not specified, the default precision 1 is used. The value of n is less than 10485761.	The maximum size is 10 MB.
VARCHAR(n) CHARACTER VARYING(n)	Variable-length string. n indicates the byte length. The value of n is less than 10485761.	The maximum size is 10 MB.
VARCHAR2(n)	Variable-length string. It is an alias for VARCHAR(n) type, compatible with Oracle. n indicates the byte length. The value of n is less than 10485761.	The maximum size is 10 MB.
NVARCHAR2(n)	Variable-length string. n indicates the string length. The value of n is less than 10485761.	The maximum size is 10 MB.
CLOB	A big text object. It is an alias for TEXT type, compatible with Oracle.	The maximum size is 10,7373,3621 bytes (1 GB - 8203 bytes).
TEXT	Variable-length string.	The maximum size is 10,7373,3621 bytes (1 GB - 8203 bytes).

In addition to the size limitation on each column, the total size of each tuple is 1,073,733,621 bytes (1 GB – 8023 bytes).

GaussDB(DWS) has two other fixed-length character types, as listed in Table 2.

The name type is used only in the internal system catalog as the storage identifier. The length of this type is 64 bytes (63 characters plus the terminator). This data type is not recommended for common users. When the name type is aligned with other data types (for example, in multiple branches of case when, one branch returns the name type and other branches return the text type), the name type may be aligned but characters may be truncated. If you do not want to have 64-bit truncated characters, you need to forcibly convert the name type to the text type.

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:
CREATE TABLE char_type_t1 
(
    CT_COL1 CHARACTER(4)
) DISTRIBUTE BY HASH (CT_COL1);

--Insert data:
INSERT INTO char_type_t1 VALUES ('ok');

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

-- Delete the tables:
DROP TABLE char_type_t1;

-- Create a table:
CREATE TABLE char_type_t2  
(
    CT_COL1 VARCHAR(5)
)  DISTRIBUTE BY HASH (CT_COL1);

--Insert data:
INSERT INTO char_type_t2 VALUES ('ok');

INSERT INTO char_type_t2 VALUES ('good');

-- Specify the type length. An error is reported if an inserted string exceeds this length.
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.
INSERT INTO char_type_t2 VALUES ('too long'::varchar(5));

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

-- Delete data:
DROP TABLE char_type_t2;