Data type mappings between Vertica and Oracle

Oracle uses proprietary data types for all main data types, such as VARCHAR, INTEGER, FLOAT, DATE.

Oracle uses proprietary data types for all main data types, such as VARCHAR, INTEGER, FLOAT, DATE. Before migrating a database from Oracle to Vertica, first convert the schema to minimize errors and time spent fixing erroneous data issues.

The following table compares the behavior of Oracle data types to Vertica data types.

Oracle Vertica Notes

NUMBER

(no explicit precision)

 INTEGER

In Oracle, the NUMBER data type with no explicit precision stores each number N as an integer M, together with a scale S. The scale can range from -84 to 127, while the precision of M is limited to 38 digits. Thus:

N = M * 10^S

When precision is specified, precision/scale applies to all entries in the column. If omitted, the scale defaults to 0.

For the common case—Oracle NUMBER with no explicit precision used to store only integer values—the Vertica INTEGER data type is the most appropriate and the fastest equivalent data type. However, INTEGER is limited to a little less than 19 digits, with a scale of 0:

[-9223372036854775807,+9223372036854775807]

NUMERIC

If an Oracle column contains integer values outside of this range:

[-9223372036854775807, +9223372036854775807]

then use the Vertica data type NUMERIC(p,0) where p is the maximum number of digits required to represent values of the source data.

If the data is exact with fractional places—for example dollar amounts—Vertica recommends NUMERIC(p,s) where p is the precision (total number of digits) and s is the maximum scale (number of decimal places).

Vertica conforms to standard SQL, which requires that p ≥ s and s ≥ 0. Vertica's NUMERIC data type is most effective for p=18, and increasingly expensive for p=37, 58, 67, etc., where p ≤ 1024.

FLOAT Even though no explicit scale is specified for an Oracle NUMBER column, Oracle allows non-integer values, each with its own scale. If the data stored in the column is approximate, Vertica recommends using the Vertica data type FLOAT, which is standard IEEE floating point, like ORACLE BINARY_DOUBLE.

NUMBER(P,0)

P ≤ 18

 INTEGER For Oracle NUMBER data types with 0 scale and a precision less than or equal to 18, use the Vertica INTEGER data type.

NUMBER(P,0)

P > 18

 NUMERIC(p,0) In the rare case where a Oracle column specifies precision greater than 18, use the Vertica data type NUMERIC(p, 0), where p = P.

NUMBER(P,S)

All cases other than above

 NUMERIC(p,s)
  • When P ≥ S and S ≥ 0, use p = P and s = S, unless the data allows reducing P or using FLOAT as discussed above.
  • If S > P, use p = S, s = S.
  • If S < 0, use p = P – S, s = 0.
FLOAT
NUMERIC(P,S) Rarely used in Oracle, see notes for Oracle NUMBER.
DECIMAL(P,S) Synonym for Oracle NUMERIC.
BINARY_FLOAT FLOAT Same as FLOAT(53) or DOUBLE PRECISION
BINARY_DOUBLE FLOAT Same as FLOAT(53) or DOUBLE PRECISION
RAW VARBINARY

Maximum sizes compared:

  • Oracle RAW data type: 2000 bytes

  • Vertica VARBINARY: 65000 bytes

LONG RAW LONG VARBINARY

Maximum sizes compared:

  • Oracle’s LONG RAW is 2GB

  • Vertica LONG VARBINARY is 32M bytes/octets (~30MB)

CHAR(n) CHAR(n)

Maximum sizes compared:

  • Oracle CHAR: 2000 bytes

  • Vertica CHAR : 65000 bytes

NCHAR(n) CHAR(*n**3) Vertica supports national characters with CHAR(n) as variable-length UTF8-encoded UNICODE character string. UTF-8 represents ASCII in 1 byte, most European characters in 2 bytes, and most oriental and Middle Eastern characters in 3 bytes.
VARCHAR2(n) VARCHAR(n)

Maximum sizes compared:

  • Oracle VARCHAR2: 4000 bytes

  • Vertica VARCHAR: 65000 bytes

NVARCHAR2(n) VARCHAR(*n**3) See notes for NCHAR.
DATE TIMESTAMP Oracle’s DATE is different from the SQL standard DATE data type implemented by Vertica. Oracle’s DATE includes the time (no fractional seconds), while Vertica DATE data types include only date as per the SQL standard.
DATE
TIMESTAMP TIMESTAMP TIMESTAMP defaults to six places—that is, to microseconds.
TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE TIME ZONE defaults to the currently SET or system time zone.
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH As per the SQL standard, you can qualify Vertica INTERVAL data types with the YEAR TO MONTH subtype.
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND The default subtype for Vertica INTERVAL data types is DAY TO SECOND.
CLOB LONG VARCHAR

You can store a CLOB (character large object) or BLOB (binary large object) value in a table or in an external location. The maximum size of a CLOB or BLOB is 128 TB.

You can store Vertica LONG data types only in LONG VARCHAR and LONG VARBINARY columns. The maximum size of LONG data types is 32M bytes.

BLOB LONG VARBINARY
LONG LONG VARCHAR

Oracle recommends using CLOB and BLOB data types instead of LONG and LONG RAW data types.

An Oracle table can contain only one LONG column, The maximum size of a LONG or LONG RAW data type is 2 GB.

LONG RAW LONG VARBINARY