Thanks! Once I started implementing it, I realized I also needed to exclude rows in which position is ‘Unassigned’ which is the default value. Here is what I finally used:

  def up do
    create unique_index(:roster_positions, [:position, :fantasy_team_id],
      where: "status LIKE 'active' AND position != 'Unassigned'")
  end

  def down do
    drop unique_index(:roster_positions, [:position, :fantasy_team_id],
      where: "status LIKE 'active' AND position != 'Unassigned'")
  end

Thanks for the help!!

On using ecto_enum and if you are using PostgreSQL, I highly recommend setting up PostgreSQL enum type in the database itself, helps you to pack the data even tighter and makes dumps readable!

Wouldn’t it be better to do an exact comparison on status? If that field only has active/inactive states I suggest using a boolean, but if its more than that I suggest using ecto_enum as it will allow you to operate w/ obvious values like :active/:pending/:inactive but uses integers in the database. More efficient for the DB and cleaner data.