search

cybertec-postgresql / db_migrator

Other
21 stars 8 forks source link

Non case sensitive operator !~* #9

Closed fljdin closed 1 year ago

fljdin commented 1 year ago

Hi Laurenz,

My new docker environment thrown collation errors with mysql_fdw, like this:

SELECT condition FROM fdw_stage.checks WHERE condition !~ '^"[^"]*" IS NOT NULL' ;
ERROR:  failed to prepare the MySQL query: 
Character set 'utf8mb3_bin' cannot be used in conjunction with 'binary' in call to regexp_like.

This piece of SQL is extracted from db_migrator in refresh "checks" table stage. After some research, it's appeared that !~ operator is manageb by mysql_fdw as a NOT REGEXP BINARY statement ^1.

What do you think about using non case sensitive operator !~*? It fixed my problem. Regard, Florent

laurenz commented 1 year ago

I did it like you suggested. !~* is more appropriate anyway. I hope that operator won't cause problems with other encodings.