redshift equivalent of TEXT data type

Question

What's the best data type to use for a column in a redshift table that will hold a very long string (can be up to 50KB)?

TEXT is replaced by varchar(256) by default. For now I used varchar(65535), but I'm not sure if that's the right way to do that...

Thanks

it would be helpful to know why you're trying to jam article-sized text into columns... — Serban Tanasa, Jun 18 '15 at 15:22
@SerbanTanasa - It's a very big table - around 100G that I need to be able to query. One of the fields is free text and can be very long. If you can suggest a better way to do that I'd love to hear it. — WeaselFox, Jun 22 '15 at 07:01

score 30 · Answer 1 · edited Aug 28 '17 at 20:18

30

Text Data Type in Redshift:

For Redshift Text Data Type you can use varchar(max) or varchar(65535)

That is the maximum length of a text data type field in Redshift.

edited Aug 28 '17 at 20:18

answered Aug 22 '17 at 21:30

Tech Support

2

Nevermind, found it in the answer below: http://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html – Marco Roy Oct 18 '17 at 19:06
just curious, if it's a numerical limit, why 65535 and not 65536? – Dash Winterson Feb 24 '22 at 20:20

score 4 · Answer 2 · answered Jun 18 '15 at 15:19

4

VARCHAR, CHARACTER VARYING and NVARCHAR can have a length of up to 65k.

answered Jun 18 '15 at 15:19

Peanut

yes, I saw these docs, see my edit. Still not sure that's the best way to store this data. – WeaselFox Jun 18 '15 at 15:20
I have not found any other types that can hold more data. I don't think there are any types better suited for this. Although there might be a better way (other DBMS or a filesystem?) than to store the whole text inside one column. – Peanut Jun 18 '15 at 15:24
this would mean I suppose that every record in the table is allocated 65k regardless of the actual length of the text.. – WeaselFox Jun 18 '15 at 15:29
No, it does not. Read the part about varchar in the link above: > Use a VARCHAR or CHARACTER VARYING column to store variable-length strings with a fixed limit. These strings are not padded with blanks, so a VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters. – Peanut Jun 18 '15 at 15:50
It seems to be "4 bytes + total bytes for characters" according to http://dba.stackexchange.com/questions/99361/storage-size-for-varchar-length-in-redshift – Peanut Jun 19 '15 at 06:18
3

However, you should be cautious of having too large columns. Amazon themeselves recommend keeping it as small as possible, because when doing complex queries the data will not be compressed: http://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-smallest-column-size.html – user2694306 Jul 19 '16 at 14:57

2 Answers2