@LostKobrakai we’ve just tried that but get an error. Same error regardless of what we put the default in for the coalesced column.
Do you have any thoughts?
Migration:
defmodule Ev2.Repo.Migrations.UpdateProjectRoleConstraint do
use Ecto.Migration
def up do
execute("DROP INDEX IF EXISTS unique_user_department_role")
execute(
"CREATE UNIQUE INDEX unique_user_department_role ON project_roles (user_id, role_id, project_id, coalesce(department_id, -1)"
)
end
end
Produces the following error.
[info] == Running Ev2.Repo.Migrations.UpdateProjectRoleConstraint.up/0 forward
[info] execute "DROP INDEX IF EXISTS unique_user_department_role"
[info] execute "CREATE UNIQUE INDEX unique_user_department_role ON project_roles (user_id, role_id, project_id, coalesce(department_id, -1)"
** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at end of input
(ecto) lib/ecto/adapters/sql.ex:200: Ecto.Adapters.SQL.query!/5
(ecto) lib/ecto/adapters/postgres.ex:96: anonymous fn/4 in Ecto.Adapters.Postgres.execute_ddl/3
(elixir) lib/enum.ex:1925: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/adapters/postgres.ex:96: Ecto.Adapters.Postgres.execute_ddl/3
(ecto) lib/ecto/migration/runner.ex:104: anonymous fn/2 in Ecto.Migration.Runner.flush/0
(elixir) lib/enum.ex:1925: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/migration/runner.ex:102: Ecto.Migration.Runner.flush/0
(stdlib) timer.erl:181: :timer.tc/2
(ecto) lib/ecto/migration/runner.ex:26: Ecto.Migration.Runner.run/6
(ecto) lib/ecto/migrator.ex:128: Ecto.Migrator.attempt/6
(ecto) lib/ecto/migrator.ex:72: anonymous fn/4 in Ecto.Migrator.do_up/4
(ecto) lib/ecto/adapters/sql.ex:576: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
(db_connection) lib/db_connection.ex:1283: DBConnection.transaction_run/4
(db_connection) lib/db_connection.ex:1207: DBConnection.run_begin/3
(db_connection) lib/db_connection.ex:798: DBConnection.transaction/3
(ecto) lib/ecto/migrator.ex:261: anonymous fn/4 in Ecto.Migrator.migrate/4
(elixir) lib/enum.ex:1314: Enum."-map/2-lists^map/1-0-"/2
(ecto) lib/mix/tasks/ecto.migrate.ex:83: anonymous fn/4 in Mix.Tasks.Ecto.Migrate.run/2
(elixir) lib/enum.ex:765: Enum."-each/2-lists^foreach/1-0-"/2
(elixir) lib/enum.ex:765: Enum.each/2
