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:

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

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