Data Migration from GP to GBase8a - Detailed Explanation of Data Types
Tags: database, greenplum, gbasedatabase
Published: 2024-06-18 09:39:07

## 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_