FlyData Sync Data Type Mapping: PostgreSQL -> Redshift (ver. 0.3.17)

There are data type differences between PostgreSQL and Amazon Redshift. Some formats are supported, while others are not.
In order to provide a smooth replication of data from PostgreSQL to Redshift, we automatically convert PostgreSQL data types to match the closest equivalent data types supported in Amazon Redshift.

Below is a mapping of this data type conversion.

PostgreSQL Data Type

Redshift Data Type

Agent version

Notes

BIGINT INT8    
BIT INT8   *11
BIT VARYING INT8   *11
BOOLEAN BOOLEAN    
BYTEA VARCHAR(MAX)   *3
CHARACTER VARCHAR   *3, *4
CHARACTER VARYING VARCHAR   *3, *4
DATE DATE   *5
DOUBLE PRECISION FLOAT8    
INTEGER INT4    
MONEY NUMERIC   *6
NUMERIC NUMERIC    
REAL FLOAT4    
SMALLINT INT2    
TEXT VARCHAR(MAX)   *3, *4
TIME TIMESTAMP   *8, *9, *10
TIME WITH TIME ZONE TIMESTAMP   *8, *9, *10
TIMESTAMP TIMESTAMP   *5
TIMESTAMP WITH TIME ZONE TIMESTAMP   *5
BOX, CIDR, CIRCLE, INET, INTERVAL, LINE, LSEG, MACADDR, PATH, POINT, POLYGON, TSQUERY, TSVECTOR, TXID_SNAPSHOT, UUID, XML VARCHAR(MAX)   *3
BIGSERIAL, SMALLSERIAL, SERIAL (unsupported)    

Notes

  • *1) Redshift does not support unsigned types
  • *2) Binary bytes get translated into a string of “0xFFFF…” format. e.g.) Binary 40bc8f => “0x40bc8f”
  • *3) Truncated at 65,535 which is the max length of Redshift VARCHAR type
  • *4) String gets converted to UTF-8
  • *5) ‘0000-00-00’ becomes ‘0001-01-01’
  • *6) maximum (precision, scale) is (38, 37)
  • *7) label text is stored as varchar string
  • *8) ‘00:00:00’ gets converted to ‘0001-01-01 00:00:00’
  • *9) Negative value gets converted as an offset from ‘0001-01-01 00:00:00’. For example, ‘-01:00:00’ becomes ‘0001-12-31 23:00:00 BC’
  • *10) Time value whose hour part is more than 23 is represented using the day part. For example, ‘25:00:00’ becomes ‘0001-01-02 01:00:00’
  • *11) ex. b’101’ becomes 5 in Redshift
  • *12) DATE(2) and DATE(4) are supported

FlyData handles real-time replication for Amazon RDS and Aurora, MySQL and PostgreSQL.

Get set up in minutes. Start uncovering data to make faster, better business decisions today.

By using this website you agree to accept our Privacy Policy and Terms & Conditions