When handling large strings in Amazon Redshift, it seems you shouldn’t use the TEXT type.

According to the official documentation, the TEXT type is converted to VARCHAR(256), so if you use it with the same expectations as MySQL or PostgreSQL, you might end up handling shorter strings than expected.

TEXT Type

You can create Amazon Redshift tables using TEXT columns, but these columns are converted to VARCHAR(256) columns that accept variable-length values with a maximum of 256 characters.

• TEXT 型および BPCHAR 型 : 文字型 - Amazon Redshift

When the data I’m storing in Redshift is logs, I add columns that might get reasonably long but I’m not sure exactly how long as VARCHAR(1024).

Note

The CREATE TABLE syntax supports the MAX keyword for character data types. For example:

create table test(col1 varchar(max));

MAX setting defines the column width. For CHAR it’s 4096 bytes, and for VARCHAR it’s 65535.

• ストレージと範囲 : 文字型 - Amazon Redshift

It seems you can also use MAX, but in most cases, you probably won’t use 65535 bytes.

What I wanted to convey in this article is: “Instead of using TEXT type based on assumptions, read the official documentation properly.”

That’s all from the Gemba.