Repository: tobymao/sqlglot

Issue:
Bug in case insensitive regex operator translation from postgres to duckdb

**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.
```python
import sqlglot
q = "SELECT value ~* 'exp' FROM a"
sqlglot.parse_one(q, dialect='postgres').sql(dialect='duckdb')
``` 
The output is: 
 "SELECT REGEXP_I_LIKE(value, 'exp') FROM a"

While the expected output is:
"SELECT REGEXP_MATCHES(value, 'exp', 'i') FROM a"


**Official Documentation**
1. postgres docs - https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-TABLE
2. duckdb docs - https://duckdb.org/docs/sql/functions/regular_expressions.html

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 33a9366029..139f803a0a 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -608,6 +608,9 @@ class Generator(generator.Generator):
                 e.args.get("modifiers"),
             ),
             exp.RegexpLike: rename_func("REGEXP_MATCHES"),
+            exp.RegexpILike: lambda self, e: self.func(
+                "REGEXP_MATCHES", e.this, e.expression, exp.Literal.string("i")
+            ),
             exp.RegexpSplit: rename_func("STR_SPLIT_REGEX"),
             exp.Return: lambda self, e: self.sql(e, "this"),
             exp.ReturnsProperty: lambda self, e: "TABLE" if isinstance(e.this, exp.Schema) else "",


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 69a592d403..959a1b3fef 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -903,6 +903,13 @@ def test_duckdb(self):
             },
         )
 
+        self.validate_all(
+            "SELECT REGEXP_MATCHES('ThOmAs', 'thomas', 'i')",
+            read={
+                "postgres": "SELECT 'ThOmAs' ~* 'thomas'",
+            },
+        )
+
     def test_array_index(self):
         with self.assertLogs(helper_logger) as cm:
             self.validate_all(