Data type differences between Amazon Redshift and supported PostgreSQL and MySQL databases - Amazon Redshift

Data type differences between Amazon Redshift and supported PostgreSQL and MySQL databases

The following table shows the mapping of an Amazon Redshift data type to a corresponding Amazon RDS PostgreSQL or Aurora PostgreSQL data type.

Amazon Redshift data type RDS PostgreSQL or Aurora PostgreSQL data type Description
SMALLINT SMALLINT Signed two-byte integer
INTEGER INTEGER Signed four-byte integer
BIGINT BIGINT Signed eight-byte integer
DECIMAL DECIMAL Exact numeric of selectable precision
REAL REAL Single precision floating-point number
DOUBLE PRECISION DOUBLE PRECISION Double precision floating-point number
BOOLEAN BOOLEAN Logical Boolean (true/false)
CHAR CHAR Fixed-length character string
VARCHAR VARCHAR Variable-length character string with a user-defined limit
DATE DATE Calendar date (year, month, day)
TIMESTAMP TIMESTAMP Date and time (without time zone)
TIMESTAMPTZ TIMESTAMPTZ Date and time (with time zone)
GEOMETRY PostGIS GEOMETRY Spatial data

The following RDS PostgreSQL and Aurora PostgreSQL data types are converted to VARCHAR(64K) in Amazon Redshift:

  • JSON, JSONB

  • Arrays

  • BIT, BIT VARYING

  • BYTEA

  • Composite types

  • Date and time types INTERVAL, TIME, TIME WITH TIMEZONE

  • Enumerated types

  • Monetary types

  • Network address types

  • Numeric types SERIAL, BIGSERIAL, SMALLSERIAL, and MONEY

  • Object identifier types

  • pg_lsn type

  • Pseudotypes

  • Range types

  • Text search types

  • TXID_SNAPSHOT

  • UUID

  • XML type

The following is prerelease documentation for the federated query to MySQL feature for Amazon Redshift, which is in preview release. The documentation and the feature are both subject to change. We recommend that you use this feature only with test clusters, and not in production environments. For preview terms and conditions, see Beta Service Participation in AWS Service Terms.

The following table shows the mapping of an Amazon Redshift data type to a corresponding Amazon RDS MySQL or Aurora MySQL data type.

Amazon Redshift data type RDS MySQL or Aurora MySQL data type Description
TINYINT(1) BOOLEAN Logical Boolean (true or false)
TINYINT(UNSIGNED) SMALLINT Signed two-byte integer
SMALLINT SMALLINT Signed two-byte integer
SMALLINT UNSIGNED INTEGER Signed four-byte integer
MEDIUMINT (UNSIGNED) INTEGER Signed four-byte integer
INT INTEGER Signed four-byte integer
INT UNSIGNED BIGINT Signed eight-byte integer
BIGINT BIGINT Signed eight-byte integer
BIGINT UNSIGNED DECIMAL Exact numeric of selectable precision
DECIMAL(M,D) DECIMAL Exact numeric of selectable precision
FLOAT REAL Single precision floating-point number
DOUBLE DOUBLE PRECISION Double precision floating-point number
CHAR CHAR Fixed-length character string
VARCHAR VARCHAR Variable-length character string with a user-defined limit
DATE DATE Calendar date (year, month, day)
TIMESTAMP TIMESTAMP Date and time (without time zone)

The following RDS MySQL and Aurora MySQL data types are converted to VARCHAR(64K) in Amazon Redshift:

  • BIT

  • BINARY

  • VARBINARY

  • TINYBLOB, BLOG, MEDIUMBLOB, LONGBLOB

  • TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

  • ENUM

  • SET

  • SPATIAL

  • TIME

  • YEAR

  • DATETIME