Amazon Redshift Mapping Columns

2 Minutes to read

Print
Share
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Type Mapping

We match Redshift data types to Rivery data types as we extract your data.

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

Rivery 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:
If you want to use SUPER datatypes, simply change the mapping from Object to JSON and the SUPER datatype will be generated.

Scenarios of Data Type Conflicts

In instances where a datatype mismatch occurs, for example, when the datatype of an existing column in the source table is modified, the datatype of the corresponding column in the target table will be determined based on certain defined rules, thereby resolving the conflict. These rules ensure that the most comprehensive data type is chosen to maintain data integrity and avoid loss of information.

Examples of Data Type Conflicts

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

Conflicting Types	Inferred Type	Example Scenario
TIMESTAMP vs. DATE	TIMESTAMP	If a column in the source, initially of TIMESTAMP type, is changed to DATE, it will be converted into 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 an INTEGER is updated to SMALLINT or BOOLEAN in the source, it will be promoted to INTEGER in the target.
BIGINT vs. (INTEGER / SMALLINT / BOOLEAN)	BIGINT	If a column of BIGINT type is changed to SMALLINT, BOOLEAN, or INTEGER in the source, it will be elevated to BIGINT in the target.
DECIMAL vs. (INTEGER / BIGINT / SMALLINT / BOOLEAN)	DECIMAL	A column that is INTEGER or DECIMAL in the source will be transformed 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 is altered to REAL type in the source, it will remain as REAL in the target.
FLOAT vs. (INTEGER / BIGINT / SMALLINT / DECIMAL / REAL / DOUBLE PRECISION / BOOLEAN)	FLOAT	A source column initially of FLOAT type that is updated to INTEGER will remain as FLOAT in the target.
VARCHAR vs. ALL DATATYPES	VARCHAR	If a column in the source was VARCHAR type and has been updated to INTEGER type, the target column will still retain 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, it will still be treated as OBJECT type in the target.

Please Note:

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

Was this article helpful?

What's Next

Amazon S3 as a Target

Table of contents

Type Mapping