Data Types

Updated on 2024-12-06 GMT+08:00

View PDF

**Table 1** Numeric types
No.	Oracle Database	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 p and scale s in GaussDB are greater than those in Oracle Database. In GaussDB, the default value of p when NUMBER does not contain parameters is much greater than the maximum boundary value when NUMBER contains parameters. However, in Oracle Database, the former is equal to the latter. In GaussDB, the value of s cannot be negative. In Oracle Database, a negative s value is accurate to an integer.
2	FLOAT [ ( p ) ]	Supported.	-
3	BINARY_FLOAT	Not supported.	-
4	BINARY_DOUBLE	Supported.	-

**Table 2** Date and time types
No.	Oracle Database	GaussDB	Difference
1	DATE	Supported, with differences.	The precision is different. GaussDB supports a larger time range than Oracle Database.
2	TIMESTAMP [ ( fractional_seconds_precision ) ]	Supported, with differences.	-
3	TIMESTAMP [ ( fractional_seconds_precision ) ] WITH TIME ZONE	Supported, with differences.	The timestamptz of GaussDB is equivalent to the timestampwithloacltimezone of Oracle Database. The type corresponding to timestamptz of Oracle Database is missing. Time zone update: In some countries or regions, the time zone information is regularly updated. Therefore, the database system often needs to modify the time zone file accordingly 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 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.	-

NOTE:

In ORA-compatible mode, the DATE type is replaced by TIMESTAMP(0) WITHOUT TIME ZONE. The differences between DATE and TIMESTAMP(0) WITHOUT TIME ZONE are the same.
In terms of TIMESTAMP [ ( fractional_seconds_precision ) ] and TIMESTAMP [ ( fractional_seconds_precision ) ] WITH TIME ZONE, the differences between GaussDB and Oracle Database are as follows:
- The value of fractional_seconds_precision ranges from 0 to 6 in GaussDB, but ranges from 0 to 9 in Oracle Database.
- GaussDB uses DateStyle to set the display format of date and time values and the rules of resolving ambiguous values. For details, see "SQL Reference > Data Type > Date/Time Types" in Developer Guide. Generally, the input format verification and output display in Oracle Database are controlled by the NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT parameters.
- By default, GaussDB removes zeros from the end of the decimal part of the second. Oracle Database controls the display of the decimal part based on the setting (FF/FF1-FF9) of the formatting parameter. For example, '2017-09-01 10:32:19.212000' is displayed as '2017-09-01 10:32:19.212' in GaussDB. In Oracle Database, it is displayed as '2017-09-01 10:32:19.212' if the format parameter contains FF, or '2017-09-01 10:32:19.212000000' if the format parameter contains FF9.
- GaussDB supports a wider time range than Oracle Database.

**Table 3** Character types
No.	Oracle Database	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 Database, however, the unit of size can be selected between BYTE and CHAR. If MAX_STRING_SIZE is set to EXTENDED, the maximum size is 32767 bytes. If MAX_STRING_SIZE is set to STANDARD, the maximum size is 4000 bytes. The actual number of characters that can be contained 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 Database, NVARCHAR2(n) is different from VARCHAR2(n). When MAX_STRING_SIZE is set to EXTENDED, the maximum size is 32767 bytes. When MAX_STRING_SIZE is set to STANDARD, the maximum size is 4000 bytes. The actual number of characters that can be contained depends on the character set in use.
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 Database, 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 Database, however, the unit of size is character, and the maximum size is 2000 bytes. The actual number of characters that can be contained depends on the character set in use.
5	CLOB	Supported, with differences.	Locators are not supported.
6	NCLOB	Not supported.	-
7	LONG	Not supported.	-

**Table 4** Binary types
No.	Oracle Database	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 Database	GaussDB
1	ROWID	Not supported.
2	UROWID	Not supported.

**Table 6** User-defined types
No.	Oracle Database	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 Database	GaussDB
1	anytype	Not supported.
2	anydata	Not supported.
3	anydataset	Not supported.

**Table 8** XML types
No.	Oracle Database	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 Database	GaussDB
1	SDO_GEOMETRY	Not supported.
2	SDO_TOPO_GEOMETRY	Not supported.
3	SDO_GEORASTER	Not supported.

**Table 10** Lock modes
No.	Oracle Database	GaussDB
1	none	-
2	null	AccessShare
3	RS	RowShare
4	RX	RowExclusive
5	S	ShareUpdateExclusive
6	SRX	Share
7	-	ShareRowExclusive
8	X	Exclusive
9	-	AccessExclusive
10	-	INVALID NOTE: 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.