FlyData Sync
Amazon Redshift Setting
Amazon Redshift Tips

Data Type Mapping: PostgreSQL

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