Data Types for Vertica

Overview

Every column in a table has a name and a data type. The data type tells Vertica how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about Vertica data types, null and default values, and data conversions.

For more information about Vertica data types and to determine which data types are available for your version of Vertica, see your Vertica documentation.

SAS/ACCESS Interface to Vertica does not directly support any data types that are not listed below. Any columns using these types are read into SAS as character strings.

Supported Vertica Data Types

Here are the data types that the Vertica engine supports.

• Binary string data: BINARY
• String data:

  CHAR

  VARCHAR

• Numeric data:

  BOOLEAN	INT8
  DOUBLE PRECISION	SMALINT
  FLOAT	TINYINT
  FLOAT(n)	DECIMAL
  FLOAT8	NUMERIC
  REAL	NUMBER
  INTEGER | INT	MONEY
  BIGINT

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.
• Date, time, and timestamp data:

  DATE	TIME
  DATETIME	TIMESTAMP
  SMALLDATETIME

Vertica Null Values

Many relational database management systems have a special value called NULL. A DBMS NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a DBMS NULL value, it interprets it as a SAS missing value.

In most relational databases, columns can be specified as NOT NULL so that they require data (they cannot contain NULL values). When a column is specified as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column. When creating a DBMS table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

To control how the DBMS handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.