How to pass flags to re_search for Postgres

[SamEdwardes]

I am trying to write a query that will use a regular expression to filter data. I am using a postgres backend. Here is an example:

con = ibis.postgres.connect()
x = con.table("x")
ibis.to_sql(
    x
    .filter([x.body.re_search('kubernetes')])
    .limit(10)

)

SELECT
  t0.ticket_id,
  t0.body
FROM x AS t0
WHERE
  t0.body ~ 'kubernetes'
LIMIT 10

This mostly works! It performs the regex search as expected. However, I would like to add flags, such as the i flag, which would make it case insensitive. I cannot figure out how to do that.

I dug into the code and have a few findings:

• The postgres backend uses regexp_like for regex search: https://github.com/ibis-project/ibis/blob/5cb83fc86cefac59f68099039863c5b056ac1cb6/ibis/backends/postgres/compiler.py#L103C27-L103C38
• The regexp_like has the ability to accept flags: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP

regexp_like('Hello World', 'world')       false
regexp_like('Hello World', 'world', 'i')  true

Is there a clean way to pass flags with ibis?

[cpcloud]

We don't expose this in any of our regular expression APIs. It's probably worth exploring how these flags are exposed and whether it makes sense for Ibis to expose them.

[SamEdwardes]

Thanks, Phillip!

Is there a way to know what settings are being used for my regex search? If I use a Postgres backend, will the regex be whatever the default for Postgres is?

+1. It would be awesome to have a way to expose flags in the future. Please let me know if creating an issue (or anything else) to track this would be helpful.

[cpcloud]

For flags we try to make the behavior analogous to closest Python standard library equivalent. In the postgres case, I believe that means passing the 'g' flag in a couple places.

[cpcloud]

@SamEdwardes It would be helpful to open an issue for this feature!

[SamEdwardes]

Thank you. Issue created here: #9274