Problem: I receive (Postgrex.Error) Error 2201B (invalid_regular_expression) invalid regular expression: brackets [] not balanced for a constraint on one of my tables despite the regex running as expected in tests raw SQL in the Postgres shell.

Context: Im building a table for message attachments. The attachments can have a filename, that field has a constraint to ensure it matches the form of a filename in my app. The regex I’m using is [^\\]*\.(\w+)$ which admittedly I sourced from this stack overflow post.

What I’ve tried: I tested the regex on regex101 and in the Postgres shell and it worked as intended. The problem is in my migration, I know this because when I remove the constraint everything works perfectly.

I also tried the solution in this DBA stack exchange post.

Simplified table declaration:

defmodule MyApp.Repo.Migrations.CreateAttachmentsTable do
  use Ecto.Migration

  @filename_with_extension_regex "[^\\]*\.(\w+)$"

  def change do
    create table(:attachments) do
      add :filename, :string, null: false
      add :message_id, references(:messages, on_delete: :delete_all), null: false

      timestamps(created_at: false, updated_at: false) # Takes the time signature of the message
    end

    create unique_index(:attachments, [:filename, :message_id], name: :attachments_filename_message_id_index)
    create constraint(:attachments, :filename_must_be_valid_filepath, check: "filename ~* '#{@filename_with_extension_regex}'")
  end
end