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