Help Center/ GaussDB/ Service Overview/ Compatibility with Oracle/ Distributed Instances/ Basic SQL Elements/ Data Types
Updated on 2024-05-31 GMT+08:00
View PDF

Data Types

Table 1 Numeric types

No.

Oracle

GaussDB

Difference

1

NUMBER [ ( p [ , s ] ) ]

Supported, with differences.

The precision and usage are different.

  • When NUMBER contains parameters, the maximum boundary values of precision and scale in GaussDB are greater than those in Oracle.
  • When NUMBER does not contain parameters, the default value of precision in GaussDB is much greater than the maximum boundary value when NUMBER contains parameters. In Oracle, the default value of precision is equal to the maximum boundary value when NUMBER contains parameters.
  • GaussDB does not support negative scale values. In Oracle, a negative scale value is accurate to the corresponding integer.

2

FLOAT [ ( p ) ]

Supported.

-

3

BINARY_FLOAT

Not supported.

-

4

BINARY_DOUBLE

Supported.

-
Table 2 Date and time types

No.

Oracle

GaussDB

Difference

1

DATE

Supported, with differences.

The precision is different. GaussDB supports a larger time range than Oracle.

2

TIMESTAMP [ ( fractional_seconds_precision ) ]

Supported.

-

3

TIMESTAMP [ ( fractional_seconds_precision ) ] WITH TIME ZONE

Supported, with differences.

The timestamptz of GaussDB is equivalent to the timestampwithloacltimezone of Oracle. The type corresponding to timestamptz of Oracle is missing.

Time zone update: In some countries or regions, time zone information is frequently updated due to political, economic, war, or other factors. Therefore, the database system often needs to modify the time zone file to ensure that the time is correct.

Currently, the GaussDB time zone type involves only timestamp with timezone. When a new time zone file takes effect, the existing data is not changed, and the new data is adjusted based on the time zone file information. The capability of processing the same type of data in the database is different from that in the Oracle database.

4

TIMESTAMP [ ( fractional_seconds_precision ) ] WITH LOCAL TIME ZONE

Not supported.

-

5

INTERVAL YEAR [ ( year_precision ) ] TO MONTH

Supported.

-

6

INTERVAL DAY [ ( day_precision ) ] TO SECOND [ ( fractional_seconds_precision ) ]

Supported.

-
Table 3 Character types

No.

Oracle

GaussDB

Difference

1

VARCHAR2 ( size [ BYTE | CHAR ] )

Supported, with differences.

In GaussDB, the unit of size is byte. That is, only BYTE is supported. You cannot select a value between BYTE and CHAR. The maximum size is 10 MB. In Oracle, however, the unit of size can be selected between BYTE and CHAR. The maximum size depends on the character set in use.

2

NVARCHAR2 ( size )

Supported, with differences.

In GaussDB, NVARCHAR2(n) is the alias of VARCHAR2(n). In Oracle, NVARCHAR2(n) is different from VARCHAR2(n).

3

CHAR [ ( size [ BYTE | CHAR ] ) ]

Supported, with differences.

In GaussDB, the unit of size is byte. That is, only BYTE is supported. You cannot select a value between BYTE and CHAR. The maximum size is 10 MB. In Oracle, however, the unit of size can be selected between BYTE and CHAR. The maximum size is 2000 bytes. The actual number of characters that can be contained depends on the character set in use.

4

NCHAR [ ( size ) ]

Supported, with differences.

In GaussDB, the unit of size is byte, and the maximum size is 10 MB. In Oracle, however, the unit of size is character, and the maximum size depends on the character set in use.

5

CLOB

Supported, with differences.

The locator concept is not supported.

6

NCLOB

Not supported.

-

7

LONG

Not supported.

-
Table 4 Binary types

No.

Oracle

GaussDB

Difference

1

RAW ( size )

Supported, with differences.

In GaussDB, size indicates the recommended byte length and is not used to verify the byte length of the input raw type.

2

LONG RAW

Not supported.

-

3

BLOB

Supported.

-

4

BFILE

Not supported.

-
Table 5 ROWID types

No.

Oracle

GaussDB

1

ROWID

Not supported.

2

UROWID

Not supported.
Table 6 User-defined types

No.

Oracle

GaussDB

1

Object types

Not supported.

2

REF data types

Not supported.

3

Variable arrays

Supported.

4

Nested tables

Supported.
Table 7 Pseudo-types

No.

Oracle

GaussDB

1

anytype

Not supported.

2

anydata

Not supported.

3

anydataset

Not supported.
Table 8 XML types

No.

Oracle

GaussDB

Difference

1

XMLTYPE

Supported, with differences.

GaussDB does not support some operations. For example, the XMLELEMENT function is used to convert a character string to the XML type instead of the XMLType type. For details, see "SQL Reference > Data Type > XMLTYPE" in Developer Guide.

2

URIType

Not supported.

-
Table 9 Spatial types

No.

Oracle

GaussDB

1

SDO_GEOMETRY

Not supported.

2

SDO_TOPO_GEOMETRY

Not supported.

3

SDO_GEORASTER

Not supported.
Table 10 Lock modes

Level

Oracle

GaussDB

Remarks

-

-

NULL

NULL of GaussDB indicates that no lock is assigned, which corresponds to the level-0 lock of Oracle.

0

none

INVALID

INVALID of GaussDB indicates that an invalid lock is assigned. An invalid lock is assigned only when a lock that cannot be identified by GaussDB occurs during system running.

1

null

AccessShare

-

2

RS

RowShare

-

3

RX

RowExclusive

-

4

S

ShareUpdateExclusive

-

5

SRX

Share

-

6

\

ShareRowExclusive

-

7

X

Exclusive

-

8

\

AccessExclusive

-
Parent topic: Basic SQL Elements