Ecto migration is producing a bad query when modifying reference

Questions / Help
1

Hey all,

I’m working on a simple chat feature for my app, but having trouble writing a migration.

I have a users and a messages table. The users have a reference to the last read message. I would like to change the on delete behavior of the reference so that it nilifies the last read message id if the message gets deleted. Here’s my attempt:

alter table(:users) do
  modify :last_read_message_id,
         references(:messages, on_delete: {:nilify, [:last_read_message_id]}),
         from: references(:messages)
end

Ecto produces some SQL that looks like this:

ALTER TABLE "users" DROP CONSTRAINT "users_last_read_message_id_fkey", ALTER COLUMN "last_read_message_id" TYPE bigint, ADD CONSTRAINT "users_last_read_message_id_fkey" FOREIGN KEY ("last_read_message_id") REFERENCES "messages"("id") ON DELETE SET NULL ("last_read_message_id")

And the migration produces the following error:

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "("

I’m running Ecto 3.11 and Postgres 14.9. Any ideas on what that error is referring to?

Thanks

2

{:nilify, columns} is only supported on PG 15 and up:

3 Likes
3

That’s probably it! Thanks!