Escaping causes check statement to fail

[kikijiki]

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]

I think you have to use extra()
It won't be quoted by any means

[kikijiki]

@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.

[tyt2y3]

Okay I found the context

sea-query/tests/mysql/table.rs

Lines 352 to 365 in 336bf95

	fn alter_with_check_constraint() {
	assert_eq!(
	Table::alter()
	.table(Glyph::Table)
	.add_column(
	ColumnDef::new(Glyph::Aspect)
	.integer()
	.not_null()
	.default(101)
	.check(Expr::col(Glyph::Aspect).gt(100))
	)
	.to_string(MysqlQueryBuilder),
	r#"ALTER TABLE `glyph` ADD COLUMN `aspect` int NOT NULL DEFAULT 101 CHECK (`aspect` > 100)"#,
	);

[tyt2y3]

So you can actually

.check(Expr::col(User::Email).binary(PgBinOper::RegexCaseInsensitive, Expr::value("^[^@]+@[^@]+\\.[^@]+$"))

[tyt2y3]

We should add an example though.