Amazon Redshift mapping columns

Type mapping

The platform matches Redshift data types to Data Integration data types while extracting your data.

The following table shows the mapping of Redshift data types to Data Integration-compatible types:

Data Integration Type	Amazon Redshift Type
TIMESTAMP	INTERVAL
FLOAT	DOUBLE_PRECISION
FLOAT	FLOAT
STRING	CHAR
DATE	DATE
STRING	VARCHAR
TIMESTAMP	TIMESTAMP
STRING	ENUM
BOOLEAN	BOOLEAN
STRING	ARRAY
STRING	array
STRING	BYTEA
BOOLEAN	BIT
FLOAT	REAL
INTEGER	BIGINT
STRING	TEXT
INTEGER	SMALLINT
FLOAT	NUMERIC
INTEGER	INTEGER
STRING	CIDR
STRING	DropEnumType
STRING	INET
STRING	MACDDR
STRING	OID
STRING	TSVECTOR
STRING	UUID
INTEGER	DATERANGE
INTEGER	INT4RANGE
INTEGER	INT8RANGE
STRING	JSON
STRING	JSONB
FLOAT	NUMRANGE
FLOAT	TSRANGE
STRING	HSTORE
JSON	SUPER

note

To use SUPER data types, change the mapping from Object to JSON, which generates the SUPER datatype.

Scenarios of data type conflicts

When a datatype mismatch occurs, such as when the existing source table column’s datatype changes, the platform determines the corresponding column’s datatype in the target table using predefined rules. These rules select the most comprehensive datatype to preserve data integrity and prevent information loss.

Examples of data type conflicts

The table below outlines scenarios of data type conflicts and the resolution rules. The Inferred Type column represents the chosen data type in the target table when there is a conflict.

Conflicting Types	Inferred Type	Example Scenario
TIMESTAMP vs. DATE	TIMESTAMP	If a source column initially of TIMESTAMP type, changed to DATE, the platform converts it to TIMESTAMP in the target.
SMALLINT vs. BOOLEAN	SMALLINT	A conflict between SMALLINT and BOOLEAN types in the source column will lead to the target column adopting the SMALLINT type.
INTEGER vs. (SMALLINT / BOOLEAN)	INTEGER	If initially an INTEGER, and changed to SMALLINT or BOOLEAN in the source, then the platform promotes it to INTEGER in the target.
BIGINT vs. (INTEGER / SMALLINT / BOOLEAN)	BIGINT	If a column of BIGINT type, changed to SMALLINT, BOOLEAN, or INTEGER in the source,elevates to BIGINT in the target.
DECIMAL vs. (INTEGER / BIGINT / SMALLINT / BOOLEAN)	DECIMAL	Transforms a column that is INTEGER or DECIMAL in the source into DECIMAL.
DOUBLE PRECISION vs. (INTEGER / BIGINT / SMALLINT / BOOLEAN)	DOUBLE PRECISION	If there is a conflict between DOUBLE PRECISION and SMALLINT in the source column, the target column will adopt DOUBLE PRECISION.
REAL vs. (DOUBLE PRECISION / DECIMAL / INTEGER / BIGINT / SMALLINT / BOOLEAN)	REAL	If a column of DOUBLE PRECISION type, and changed to REAL type in the source, it remains as REAL in the target.
FLOAT vs. (INTEGER / BIGINT / SMALLINT / DECIMAL / REAL / DOUBLE PRECISION / BOOLEAN)	FLOAT	A source column initially of FLOAT type, and updated to INTEGER remains as FLOAT in the target.
VARCHAR vs. ALL DATATYPES	VARCHAR	If a column in the source is VARCHAR type and updated to INTEGER type, the target column retains the VARCHAR type.
OBJECT vs. ALL DATATYPES	OBJECT	If a source column is initially set as OBJECT type and later changed to DATE or INTEGER, the target treats as OBJECT type.

note

VARCHAR and OBJECT have higher priority and overrides other data types in conflicts.