The unexpected varchar(255)

Questions / Help
,
#1

I am using :string as column type in a migration. But the generated raw SQL is varchar(255) rather than varchar which is declared at here.

My environment:

  • PostgreSQL 12
  • ecto 3.4.5
  • ecto_sql 3.4.4

I created a minimal migraiton like this:

defmodule SampleApp.Repo.Migrations.Test do
  use Ecto.Migration

  def change do
    create table("test_types") do
      add :string, :string, null: false
      add :varchar, :varchar, null: false
      add :text, :text, null: false

      timestamps()
    end
  end
end

Then, I inspected the generated SQL with mix ecto.migrate --log-sql, and the output is:

CREATE TABLE "test_types" (
"id" bigserial,
"string" varchar(255) NOT NULL, 
"varchar" varchar NOT NULL, 
"text" text NOT NULL, 
"inserted_at" timestamp(0) NOT NULL, 
"updated_at" timestamp(0) NOT NULL, PRIMARY KEY ("id")) []

As you see:

  • string -> varchar(255) [BAD]
  • varchar -> varchar [GOOD]
  • text -> text [GOOD]

After searching on source code of ecto and ecto_sql, I still have no idea why :string is not mapped to varchar but varchar(255).

Have any ideas?

#2

What type of database are you using? Postgres for example doesn’t have an unsized varchar, instead it uses :text.

#3

Sorry for lacking important information.

I am using PostgreSQL.

As said at here, it looks like that PostgreSQL has unsized varchar.

I am using :text, too. But, I am still curious about the behavior that I described above.

#4

For postgres there is no difference between text and an unsized varchar so I think it’s mostly about making that distinction clear, because the only time to use varchar is when you want to set the size.

1 Like
#5

Yes, looks like it is hard-coded into the postgres driver - connection.ex has the following function:

    defp column_type(type, opts) do
      size      = Keyword.get(opts, :size)
      precision = Keyword.get(opts, :precision)
      scale     = Keyword.get(opts, :scale)
      type_name = ecto_to_db(type)

      cond do
        size            -> [type_name, ?(, to_string(size), ?)]
        precision       -> [type_name, ?(, to_string(precision), ?,, to_string(scale || 0), ?)]
        type == :string -> [type_name, "(255)"]
        true            -> type_name
      end
    end

as to why, you will have to ask the author. You can override by providing a size option when defining the column by the looks of things…

3 Likes
#6

Thanks for the code. That’s what I want.

#7

Thanks for your useful suggestion.