Can openGauss Replace Oracle in OLTP? Detailed Data‑Type Compatibility Guide

Background

With increasing uncertainty from the pandemic and international tensions, the demand for domestic database solutions that can replace Oracle has grown. openGauss, the open‑source successor of GaussDB 100 released by Huawei, is positioned as a potential alternative for Oracle in critical OLTP workloads.

Compatibility Evaluation

Character Types

Oracle defines six character types: CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, and LONG. openGauss supports all except LONG, which can be substituted with CLOB. Key differences include storage size (VARCHAR stores Chinese characters as two bytes, VARCHAR2 stores all as two bytes) and handling of empty strings (VARCHAR treats them as empty, VARCHAR2 converts them to NULL).

CHAR – compatible

NCHAR – compatible

VARCHAR – compatible

VARCHAR2 – compatible

NVARCHAR2 – compatible

LONG – not compatible (use CLOB)

postgres=# create table t_char(col CHAR(20));
CREATE TABLE

postgres=# create table t_nchar(col NCHAR(20));
CREATE TABLE

postgres=# create table t_varchar(col VARCHAR(20));
CREATE TABLE

postgres=# create table t_varchar2(col VARCHAR2(20));
CREATE TABLE

postgres=# create table t_nvarchar2(col NVARCHAR2(20));
CREATE TABLE

postgres=# create table t_long(col LONG);
ERROR:  type "long" does not exist
LINE 1: create table t_long(col LONG);

Numeric Types

Oracle provides INT, NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE. openGauss supports INT, NUMBER, FLOAT, and BINARY_DOUBLE; BINARY_FLOAT is not available and should be replaced by BINARY_DOUBLE.

INT – compatible

NUMBER – compatible

FLOAT – compatible

BINARY_FLOAT – not compatible (use BINARY_DOUBLE)

BINARY_DOUBLE – compatible

postgres=# create table t_int(col int);
CREATE TABLE

postgres=# create table t_number(col NUMBER);
CREATE TABLE

postgres=# create table t_float(col float);
CREATE TABLE

postgres=# create table t_bfloat(col BINARY_FLOAT);
ERROR:  type "binary_float" does not exist
LINE 1: create table t_bfloat(col BINARY_FLOAT);

postgres=# create table t_bdouble(col BINARY_DOUBLE);
CREATE TABLE

Date & Time Types

Oracle includes DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND. openGauss supports all except TIMESTAMP WITH LOCAL TIME ZONE, which can be replaced by TIMESTAMP WITH TIME ZONE.

DATE – compatible

TIMESTAMP – compatible

TIMESTAMP WITH TIME ZONE – compatible

TIMESTAMP WITH LOCAL TIME ZONE – not compatible (use TIMESTAMP WITH TIME ZONE)

INTERVAL YEAR TO MONTH – compatible

INTERVAL DAY TO SECOND – compatible

postgres=# create table t_date(col DATE);
CREATE TABLE

postgres=# create table t_timestamp(col TIMESTAMP);
CREATE TABLE

postgres=# create table t_timezone(col TIMESTAMP WITH TIME ZONE);
CREATE TABLE

postgres=# create table t_localtimezone(col TIMESTAMP WITH LOCAL TIME ZONE);
ERROR:  syntax error at or near "WITH LOCAL"
LINE 1: create table t_localtimezone(col TIMESTAMP WITH LOCAL TIME Z...

postgres=# create table t_interval_ym(col INTERVAL YEAR TO MONTH);
CREATE TABLE

postgres=# create table t_interval_ds(col INTERVAL DAY TO SECOND);
CREATE TABLE

Large Object Types

Oracle defines BLOB, CLOB, NCLOB, and BFILE. openGauss supports BLOB and CLOB directly. NCLOB and BFILE are not supported; they can be substituted with CLOB and BLOB respectively.

BLOB – compatible

CLOB – compatible

NCLOB – not compatible (use CLOB)

BFILE – not compatible (use BLOB)

postgres=# create table t_blob(col BLOB);
CREATE TABLE

postgres=# create table t_clob(col CLOB);
CREATE TABLE

postgres=# create table t_nclob(col NCLOB);
ERROR:  type "nclob" does not exist
LINE 1: create table t_nclob(col NCLOB);

postgres=# create table t_bfile(col BFILE);
ERROR:  type "bfile" does not exist
LINE 1: create table t_bfile(col BFILE);

ROWID Types

ROWID and UROWID are Oracle‑specific pseudo‑columns. openGauss does not support them; they can be replaced with CHAR(20) or CHAR(40) respectively.

ROWID – not compatible (use CHAR(20))

UROWID – not compatible (use CHAR(40))

postgres=# create table t_rowid(col ROWID);
ERROR:  type "rowid" does not exist
LINE 1: create table t_rowid(col ROWID);

postgres=# create table t_urowid(col UROWID);
ERROR:  type "urowid" does not exist
LINE 1: create table t_urowid(col UROWID);

RAW Types

RAW and LONG RAW store variable‑length binary data. openGauss supports RAW but not LONG RAW; LONG RAW can be replaced with BLOB.

RAW – compatible

LONG RAW – not compatible (use BLOB)

postgres=# create table t_raw(col RAW(10));
CREATE TABLE

postgres=# create table t_longraw(col LONG RAW);
ERROR:  syntax error at or near "RAW"
LINE 1: create table t_longraw(col LONG RAW);

Conclusion

Overall, openGauss can fully replace Oracle for the majority of data types. Common types require no changes, while a few rarely used types need minor code adjustments with the suggested alternatives listed above.