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
