1. Overview

This section provides guidance on mapping Greenplum's standard data types to GBase database tables during the migration process. There are four main categories of data types:

• Binary data types
• Character data types
• Numeric data types
• Date/time data types

While most of Greenplum's built-in types can be replaced by corresponding types in GBase8a, certain types (such as geometric types, network address types, text search types, and custom types) do not have standard mappings. These require selecting appropriate GBase types based on the application's needs and possibly adjusting the related application code accordingly.

2. Binary Data Type Migration

2.1 BYTEA Data Type

In Greenplum, the bytea type is used to store binary data such as Word, Excel documents, and image files, with a maximum size of 1GB. The equivalent type in GBase is LONGBLOB, but its maximum size is only 64MB.

The bytea type in Greenplum can be formatted in either hexadecimal or escape format. The escape format uses ASCII character sequences to represent binary data, which can be convenient but may blur the distinction between binary and character strings, making it cumbersome. Therefore, it is advisable to avoid this format in new applications when possible.

3. Character Data Types

3.1 CHARACTER VARYING(N), VARCHAR(N) Types

In Greenplum, these types store variable-length strings with a length limit and a maximum size of 10MB. The equivalent in GBase is VARCHAR or TEXT, with a maximum length of 10,922 characters.

3.2 CHARACTER, CHAR(N) Types

These are used for fixed-length strings in Greenplum, padding with spaces if necessary, with a maximum size of 10MB. The GBase equivalent is CHAR (up to 255 bytes) or TEXT.

3.3 TEXT Type

In Greenplum, this type stores variable-length strings without a length limit. The GBase equivalent is TEXT, with a maximum length of 10,922 characters.

4. Numeric Data Types

4.1 SMALLINT

A standard SQL data type with a storage length of 2 bytes, ranging from -32,768 to +32,767. In GBase, use the SMALLINT type.

4.2 INT, INTEGER

A standard SQL data type with a storage length of 4 bytes, ranging from -2,147,483,648 to +2,147,483,647. In GBase, use INT or INTEGER.

4.3 BIGINT

A standard SQL data type with a storage length of 8 bytes, ranging from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. In GBase, use BIGINT.

4.4 DECIMAL(p,s), NUMERIC(p,s)

A variable-length standard SQL data type. In GBase, use DECIMAL(p,s).

4.5 FLOAT[(precision)]

A standard SQL data type. In GBase, use FLOAT(precision).

4.6 REAL

A standard SQL data type. In GBase, use a high-precision DECIMAL data type.

4.7 DOUBLE PRECISION

A standard SQL data type. In GBase, use DOUBLE.

5. TIMESTAMP Data Types

5.1 P[(precision)]

Stores date and time values including year, month, day, hour, minute, and second. Precision ranges from 0 to 9, with a default of 6. GBase equivalent: TIMESTAMP, but only precise to seconds, ranging from January 1, 1970, to January 19, 2038.

5.2 TIMESTAMP[(precision)] WITH TIME ZONE

Stores timestamp values with time zone. GBase equivalent: TIMESTAMP, but does not retain time zone information.

5.3 DATE

Stores date values comprising year, month, and day. Year range: 0001 to 9999. GBase equivalent: DATETIME, ranging from January 1, 0001, to December 31, 9999.

5.4 TIME

Stores time values comprising hours, minutes, and seconds. GBase equivalent: TIMESTAMP.

5.5 TIME[(precision)] WITH TIME ZONE

Stores time values with time zone. GBase equivalent: TIMESTAMP, but does not retain time zone information.

GBase database products include GBase 8a (distributed logical data warehouse), GCDW (cloud-native data warehouse), GBase 8s (database cluster based on shared storage), and GBase 8c (multi-model distributed database). For more information, please visit: www.gbase.cn