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?
What type of database are you using? Postgres for example doesn’t have an unsized varchar, instead it uses :text
.
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.
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
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
Thanks for the code. That’s what I want.
Thanks for your useful suggestion.