37

ERROR 22001 (string_data_right_truncation): value too long for type character varying(255)

I understand (and assumed) that a string would be limited to a certain number of characters; however, I am not sure what type would be best for this scenario.

What type should I be using for a 'content' section of a blog in Phoenix framework?

The data will be paragraphs of text and cannot be limited in size.

Thanks in advance.

Ronan Boiteau
  • 9,608
  • 6
  • 34
  • 56
Andrew Hendrie
  • 6,205
  • 4
  • 40
  • 71

1 Answers1

68

The error you get is from the underlying database where the column type is set to varchar which is what's created by default when you specify the column type as string in a migration.

To store a variable length string over 255 characters, you need to specify the column type as text in the migration. You can convert the type of the existing column to text by using a migration such as:

alter table(:posts) do
  modify :content, :text
end

The field type in the schema section of the model should remain as string:

schema "posts" do
  field :content, :string
end
Navin Peiris
  • 2,526
  • 2
  • 24
  • 25
  • 3
    That's an interesting solution; didn't realize the `:text` type is available in migrations. – Onorio Catenacci Mar 16 '17 at 12:31
  • Yeps! There's a bit about it here: https://hexdocs.pm/ecto/Ecto.Migration.html#module-field-types – Navin Peiris Mar 16 '17 at 12:43
  • It sounds like there are NO downsides of using :text. I'm surprised it's not the default for :string - https://stackoverflow.com/a/4849030/2062384 – Freedom_Ben Mar 01 '22 at 03:42
  • 1
    @Freedom_Ben It really depends on your database constraints. If someone threw the bible into some :text field on a lot of accounts, you'd start growing your database by 1 bibles worth of data for each time someone did that. It can cause your database to get very big which might be a problem. – Sero Feb 06 '23 at 15:34