Is there a way to make text searches case-insensitive?

Question

I've just checked, and DuckDB performs text searches case-sensitively. SELECT * FROM bar WHERE foo = 'My name'; only matches My name, not my name.

Never, in the long history of ever, have I had a user ask me for case-sensitive searches. I understand that a and A are different characters to a computer, but to users they are not. The result is, the searches seem buggy as they're missing matches that the user knows are there.

Is there a way to get around this in DuckDB? Searching the site++ didn't turn up a solution.

If I remember right, MySQL has an adjustable setting for this behavior. In Postgres, which also performs case-sensitive searches, you can use the citext (case-insensitive text) extension to take out the tedium and risk of tweaking every SELECT, I believe a collation (newish, haven't tried it out), or make each and every search compatible with this:

SELECT * FROM bar WHERE lower(foo) = lower('My name');

Answer 1

You can set default_collation to nocase:

bar = duckdb.sql("""
    select * from values ('My name'),('my name') bar(foo)
""")

duckdb.sql("""
    set default_collation = 'nocase';
    
    select * from bar where foo = 'My name';
""")

┌─────────┐
│   foo   │
│ varchar │
├─────────┤
│ My name │
│ my name │
└─────────┘