Accent-insensitive queries with Postgres

[juanpablob]

Hi Payload team,

I’m looking for guidance around accent-insensitive search when using Payload with the Postgres adapter.

We have user-generated content in multiple languages, so some searchable fields contain accents or diacritics, for example:

• Ácido
• Äpfel
• Café
• Niño
• München

In the UI, we expose searchable selects / comboboxes where users often type plain input like:

• acido
• apfel
• cafe
• nino
• munchen

The problem is that a query like this:

where: {
  title: {
    contains: 'acido',
  },
}

does not match a stored value like Ácido.

From what I understand, Payload/Postgres text filtering is accent-sensitive by default, and one workaround suggested out there was to duplicate fields into normalized versions, for example:

title: 'Ácido',
normalizedTitle: 'acido'

and then query normalizedTitle.

But for our use case this is not really sustainable, because we would need to duplicate many searchable fields across collections, hooks, nested fields, localized content, tags, etc. It also means every search UI needs to know which normalized field to query.

I've also read about Postgres unaccent, but since Payload builds the SQL internally, I’m not sure if there is a clean way to use it for normal where queries. I also saw collation-based approaches, but I'm not sure whether those work correctly with contains / like queries or what side effects they may have.

So my questions are:

• Is there currently a Payload supported way to make contains queries accent-insensitive with Postgres?
• If not, is there any recommended workaround that does not require duplicating fields manually?
• Would this be something Payload might fix in v3 or introduce in v4, either at the field, collection, or database adapter level?

Thank you!

Best,
Juampi

[paulpopus]

Thanks for raising this. Today, the Postgres adapter can install the unaccent extension, but Payload does not use it when building contains or like queries.
Rather than adding a single accentInsensitive flag, we think it makes sense to add a more flexible query-operator API. The shared behavior would live in @payloadcms/drizzle, while Postgres-specific helpers would be exposed from @payloadcms/db-postgres.

This could allow a simple built-in preset:

postgresAdapter({
  extensions: ['unaccent'],
  query: {
    operatorHandlers: [postgresUnaccent()],
  },
})

The same API could also support custom SQL functions or different extensions without requiring duplicate normalized fields. We would need to cover contains, like, and not_like, as well as consider JSON/rich-text queries and indexing performance.

[paulpopus]

I went ahead and built this out here: #17741

[paulpopus]

Merged in for 4.0