Data Type Comparison Rules

1

Numeric values

Supported.

-

For comparison based on numeric values, a number close to the positive direction of a number axis is greater than a number close to the negative direction of the number axis, for example, 5 > 3, 1 > –1, and –1 > –2.

2

Datetime values

Supported.

-

For comparison based on datetime values, the later date or timestamp is greater than the earlier date or timestamp, for example, '2000-01-01 12:00:00' > '2000-01-01 11:59:59'.

3

Binary values

Supported.

-

For comparison based on binary values, a number close to the positive direction of a number axis is greater than a number close to the negative direction of the number axis, for example, 1001 0101 > 1001 0011.

4

Character values

Supported, with differences.

• The following contents are different:
  • GaussDB and Oracle support different comparison rules, and the names of the same comparison rules may be different.
  • GaussDB and Oracle differ in specifying comparison rules. For example, table-level comparison rules cannot be specified in GaussDB, but can be specified in Oracle.
  • GaussDB and Oracle differ in the syntax for specifying comparison rules. For example, in GaussDB, the ENCODING, LC_CTYPE, and LC_COLLATE parameters are used to specify the character set, character type, and comparison rules used during database creation. For details, see "SQL Reference > SQL Syntax > C > CREATE DATABASE" in Developer Guide. In Oracle, comparison rules at different levels are usually specified by a series of parameters with the NLS prefix.

• The following contents are the same:
  • In GaussDB and Oracle, character strings are compared character by character starting from the first character. If the first character is the same, the next character is compared until different characters appear or a character string ends. If all characters of two strings are the same, they are considered equal. Otherwise, the size relationship of the first found different characters is used for comparison. In addition, for some data types, such as CHAR, spaces are added to the shorter string until two strings have the same length, and then the strings are collated.
  • For single-character comparison, common comparison rules include binary collation based on the character set and monolingual collation. If the default character set and comparison rules are used, both GaussDB and Oracle use monolingual collation.
  • In GaussDB and Oracle, the comparison rules of character types are closely related to character sets. For example, most non-Unicode character sets do not support monolingual collation. Therefore, binary collation is used by default.

-

5

Object values

Not supported.

-

-

6

Varrays and nested tables

Supported, with differences.

Both Oracle and GaussDB support the comparison of varrays. Different from Oracle, GaussDB not only supports the comparison of the number of elements in two varrays, but also supports the comparison between varrays of the same type.

-

7

Data type precedence

Supported.

-

-

8

Explicit/Implicit data conversion

Supported.

-

-