Redshift Federated Query (RDS Postgres) Long Varchar Value Error (code 25101)

0

Hello!

I'm trying to query from the Postgres table using Redshift federated query option. The table has a column with JSONB data type and for some rows its length is greater than 65535. Redshift is trying to convert them into VARCHAR(65535) and raises the following issue:

Detail:
error: Value of VARCHAR type is too long.
code: 25101
context: Received VARCHAR len=109047 max=65535. This could be due to multibyte characters exceeding the column size or the value in remote database is larger than the max allowed length (65535) of varchar column in Redshift.
query: 7493043
location: federation_fetchers.hpp:146

Casts like CAST(postgres_field AS VARCHAR(65535)) or other manipulations with the length of the field do not help.

Is there any solution or workaround (to do something like the option TRUNCATECOLUMNS for the COPY command or else) to directly get the data from Postgres using federated query truncating the length automatically to fit the Redshift max column length size?

1 Answer
1

You could create a view in Postgres that extracts the elements from your JSON column as discrete columns and then query the view from Redshift. With this solution, no column of data exceeds 64K so you'll be able to query the data properly in Redshift.

profile pictureAWS
EXPERT
answered 2 years ago
  • Thanks for the answer! That's great solution, unfortunately in my case there is no ability to create new objects in source Postgres db.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions