Use smaller string column than varchar255

shijith.k · August 16, 2020, 1:52pm

I want to create a column in one of the tables with a varchar2 and not more than that. By default string in ecto migration refers varchar255. So how can I use varchar2?

Eiji · August 16, 2020, 2:07pm

In migration simply write: :"varchar(2)" as a type or "varchar(2)" in latest ecto_sql (as String was not supported before).

shijith.k · August 16, 2020, 2:15pm

Thank you

al2o3cr · August 16, 2020, 5:38pm

You can also use the size option in the migration. Here’s an example from the ecto_sql tests:

github.com

elixir-ecto/ecto_sql/blob/da96dad6275cec53541463ae057bb6c0ec49783b/test/ecto/adapters/postgres_test.exs#L1179


alias Ecto.Migration.Reference
import Ecto.Migration, only: [table: 1, table: 2, index: 2, index: 3,
                              constraint: 2, constraint: 3]

test "executing a string during migration" do
  assert execute_ddl("example") == ["example"]
end

test "create table" do
  create = {:create, table(:posts),
            [{:add, :name, :string, [default: "Untitled", size: 20, null: false]},
             {:add, :price, :numeric, [precision: 8, scale: 2, default: {:fragment, "expr"}]},
             {:add, :on_hand, :integer, [default: 0, null: true]},
             {:add, :published_at, "time without time zone", [null: true]},
             {:add, :is_active, :boolean, [default: true]},
             {:add, :tags, {:array, :string}, [default: []]},
             {:add, :languages, {:array, :string}, [default: ["pt", "es"]]},
             {:add, :limits, {:array, :integer}, [default: [100, 30_000]]}]}

  assert execute_ddl(create) == ["""
  CREATE TABLE "posts" ("name" varchar(20) DEFAULT 'Untitled' NOT NULL,

Note how name is generated as a VARCHAR(20).

Eiji · August 16, 2020, 7:48pm

Ah, completely forgot about this option, nice find. In that case it’s worth to give some links to official API:

Each of those 3 mentions :size option. Both add/3 and modify/3 provides more useful options:
https://hexdocs.pm/ecto_sql/Ecto.Migration.html#module-field-types
https://hexdocs.pm/ecto_sql/Ecto.Migration.html#add/3
https://hexdocs.pm/ecto_sql/Ecto.Migration.html#modify/3

shijith.k · August 17, 2020, 4:59am

Thank you. This is a more clean option