Data Types for Greenplum

Overview

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

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

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

Supported Greenplum Data Types

Here are the data types that SAS/ACCESS Interface to Greenplum supports:

• Character data:

  CHAR(n)

  VARCHAR(n)

  TEXT

• Numeric data:

  BIGINT	REAL
  SMALLINT	FLOAT
  INTEGER	DECIMAL | DEC | NUMERIC
  DOUBLE PRECISION

  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

  TIMESTAMP

  Note: Be aware that columns of these data types can contain data values that are out of range for SAS.
• Binary data: BYTEA

Greenplum Null Values

Greenplum has a special value called NULL. A Greenplum NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a Greenplum NULL value, it interprets it as a SAS missing value. When loading SAS tables from Greenplum sources, SAS/ACCESS stores Greenplum NULL values as SAS missing values.

In Greenplum tables, NULL values are valid in all columns by default. There are two methods to specify a column in a Greenplum table so that it requires data:

• Using SQL, you specify a column as NOT NULL. This tells SQL to allow only a row to be added to a table if a value exists for the field. Rows that contain NULL values in that column are not added to the table.
• Another approach is to assert NOT NULL DEFAULT.

When creating Greenplum tables with SAS/ACCESS, you can use the DBNULL= data set option to specify the treatment of NULL values. For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

Once you know whether a Greenplum column enables NULLs or the host system provides a default value for a column that is specified as NOT NULL WITH DEFAULT, you can write selection criteria and enter values to update a table. Unless a column is defined as NOT NULL or NOT NULL DEFAULT, it enables NULL values.

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

LIBNAME Statement Data Conversions

The following table shows the default formats that SAS/ACCESS Interface to Greenplum assigns to SAS variables when using the LIBNAME statement to read from a Greenplum table.

These default formats are based on Greenplum column attributes.

The next table shows the default Greenplum data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.