Escaping causes check statement to fail
Closed this issue · 5 comments
Description
In a migration for PostgreSQL I have some code like this:
manager
.create_table(
Table::create()
.table(User::Table)
.col(
ColumnDef::new(User::Id)
.big_integer()
.not_null()
.auto_increment()
.primary_key(),
)
.col(
ColumnDef::new(User::Email)
.string_len(320)
.check("email ~* '^[^@]+@[^@]+\\.[^@]+$'")
.unique_key()
.not_null(),
)
.to_owned(),
)
.await?;
This is the SQL that gets produced:
CREATE TABLE "user" (
"id" bigserial NOT NULL PRIMARY KEY,
"email" varchar(320) CHECK (E'email ~* ''^[^@]+@[^@]+\.[^@]+$''') UNIQUE NOT NULL,
);
The error is:
Execution Error: error returned from database: invalid input syntax for type boolean: "email ~* '^[^@]+@[^@]+\.[^@]+$'"
I tried changing the way I express the check but I could not find a way to make it work, there is always some escaping problem.
This is what would work instead:
CREATE TABLE "user" (
"id" bigserial NOT NULL PRIMARY KEY,
-- Either this
"email" varchar(320) CHECK (email ~* '^[^@]+@[^@]+\.[^@]+$') UNIQUE NOT NULL
-- Or this?
"email" varchar(320) CHECK (email ~* E'^[^@]+@[^@]+\\.[^@]+$') UNIQUE NOT NULL
);
Workarounds
Current workaround is to add the check as a separate sql statement bypassing seaorm.
Versions
sea-orm: 0.12.2
sea-orm-migration: 0.12.2
db: postgres
OS: Win11
@tyt2y3 Thank you for the quick response, I didn't know about extra()
.
That seems to do the trick too.
Not sure if this is a bug or not, feel free to close this if it is intended to behave like this.
Okay I found the context
sea-query/tests/mysql/table.rs
Lines 352 to 365 in 336bf95
So you can actually
.check(Expr::col(User::Email).binary(PgBinOper::RegexCaseInsensitive, Expr::value("^[^@]+@[^@]+\\.[^@]+$"))
We should add an example though.