Error when filtering by UUID in PostgreSQL – "function lower(uuid) does not exist"

[dikhimartin]

Hi Spatie team, thank you for the awesome package!

I'm encountering an issue when trying to filter data using a UUID field in a PostgreSQL database. Here's the scenario:

Request:

GET /api/help-center/faq?filter[topic_id]=af77649f-32a6-4d98-b831-e996e05427f0&per_page=5&page=1

Resulting Error:

{
  "status": {
    "code": 500,
    "message": "SQLSTATE[42883]: Undefined function: 7 ERROR:  function lower(uuid) does not exist\nLINE 1: ...e from \"help_center_faqs\" where \"status\" = $1 and LOWER(\"hel...\n                                                             ^\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts. (Connection: pgsql, SQL: select count(*) as aggregate from \"help_center_faqs\" where \"status\" = 1 and LOWER(\"help_center_faqs\".\"topic_id\") LIKE %af77649f-32a6-4d98-b831-e996e05427f0%)"
  }
}

Context:

• The topic_id column is of type uuid.
• PostgreSQL does not support applying LOWER() directly on uuid types.
• The error only occurs when filter is applied without any additional search term. I suspect that internally, the package uses a LIKE comparison with LOWER(...), which works on text/varchar, but not on uuid.

Environment:

• PHP 8.2
• Laravel 11
• PostgreSQL 9.5
• spatie/laravel-query-builder v6.3

Temporary Workaround:

Casting the uuid field to text manually solves the issue in raw queries, but since I'm relying on the package for query building, I’d prefer to avoid doing that manually for every filter.

---

Question:

Is there any recommended way to handle UUID filters properly in PostgreSQL using QueryBuilder?
Should the package automatically avoid using LOWER() for non-string fields like UUIDs?

Thanks in advance for any help or guidance! 🙏

[dikhimartin]

Hi, just wanted to follow up on this.

Turns out I found a solution for filtering UUIDs in PostgreSQL when using spatie/laravel-query-builder.

The issue occurred because the default filter tries to do a LOWER() + LIKE on the UUID column, which throws an error in PostgreSQL since UUIDs aren't compatible with LOWER().

Here's the fix that worked for me:

$results = QueryBuilder::for($query)
    ->allowedFilters([
        ...$fields,
        AllowedFilter::exact('topic_id'), // ✅ this solved the issue
    ])
    ->defaultSort('-created_at')
    ->allowedSorts(generateAllowedSorts($fields))
    ->paginate($request->input('per_page'))
    ->appends(request()->query());

By explicitly defining the filter as AllowedFilter::exact('topic_id'), the query avoids using LIKE and instead performs a proper match, which works well with UUIDs in PostgreSQL.

Hopefully this helps others facing similar issues!