Building off of this older post:

What I need to do is to create a few case-insensitive columns in PostGres. I’ve been following this post: Case insensitive column in Phoenix | Adam Niedzielski but somehow I always seem to find a way to take something off the rails.

In particular, it seems that any citext column cannot specify a maximum length.

Here is my schema that works, but note that for all the citext fields, I can’t specify a length.

defmodule :"Elixir.Languages.Repo.Migrations.Languages" do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION citext", "DROP EXTENSION citext"

    create table(:languages, primary_key: false) do
      add :id, :citext, null: false, primary_key: true  # 2-character ISO 639_1
      add :code_639_2t, :citext, null: false
      add :code_639_2b, :citext, null: false
      add :name, :string, size: 80, null: false
    end

    # Ensure case insensitivity for ISO identifiers
    create index(:languages, ["lower(id)"], name: :languages_index, unique: true)
    create index(:languages, ["lower(code_639_2t)"], name: :languages_639_2t_index, unique: true)
    create index(:languages, ["lower(code_639_2b)"], name: :languages_639_2b_index, unique: true)

  end
end

Before I tried the citext, I used strings with specific character limits:

add :id, :string, size: 2, null: false, primary_key: true  # 2-character ISO 639_1
add :code_639_2t, :string, size: 3, null: false
add :code_639_2b, :string, size: 3, null: false
add :name, :string, size: 80, null: false

So I guess what really bothers me about citext is that it’s essentially a TEXT field, not a simple String or VARCHAR type field. In MySQL, the text fields could be a source of inefficiencies and they seem overblown for storing 3-character strings. Does anyone have thoughts or reassurances for dealing with this?