# 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
- Redshift does not support unsigned types
- Binary bytes get translated into a string of “0xFFFF…” format. e.g.) Binary 40bc8f => “0x40bc8f”
- Truncated at 65,535 which is the max length of Redshift VARCHAR type
- String gets converted to UTF-8
- ‘0000-00-00’ becomes ‘0001-01-01’
- maximum (precision, scale) is (38, 37)
- label text is stored as varchar string
- ‘00:00:00’ gets converted to ‘0001-01-01 00:00:00’
- 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’
- 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’
- ex. b’101’ becomes 5 in Redshift
- DATE(2) and DATE(4) are supported