- Newest
- Most votes
- Most comments
Hello,
I would like to inform you that in regard to federated queries, PostgreSQL data types are converted to VARCHAR(64K) in Amazon Redshift [1] unless they can be mapped to data types in Redshift.
Unfortunately, as of now VARCHAR datatype has a hard limit on length which is 65535 bytes [2]. So for now we would have to keep the column size and values within this limit, 65535 bytes in order to resolve this error.
A possible workaround for the above can be to split the column data to multiple columns in PostgreSQL, possibly using a view, so that data in each column does not exceed the 65535 bytes limit.
With regards to using the SUPER type as a way to overcome this limit, I would like to let you know that when pulling data into a SUPER datatype column, the JSON data is parsed using json_parse(json_string) function [3], although SUPER objects and arrays are up to 1MB it is a string scalar value inside and would be constrained to the same 64K VARCHAR size limit, therefore, this would not be a feasible way to overcome the above limitation with federated queries.
Therefore, currently, the only workaround to overcome the above limitation is to create a physical table in Redshift, then get the data from the RDS/Aurora into S3 and load the data into Redshift within the SUPER type, however, if the values are larger than the 64K character limit for VARCHAR (which is imposed on the SUPER type) the limitation would still apply.
Considering the above, there is currently no way to overcome this limitation aside from possibly splitting up the columns containing the data into smaller columns which would fit within the maximum size of the VARCHAR column, but I do understand that this may not be ideal.
[1] Data type differences between Amazon Redshift and supported PostgreSQL and MySQL databases - https://docs.aws.amazon.com/redshift/latest/dg/federated-data-types.html
[2] Character types - Storage and ranges - https://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html#r_Character_types-storage-and-ranges
[3] JSON_PARSE function - https://docs.aws.amazon.com/redshift/latest/dg/JSON_PARSE.html
Relevant content
- Accepted Answerasked 3 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago