Dynamic ID (UUID) filter with text input

Queries and Resources
,
1

  • Goal: I'm trying to have an optional ID value filter for a UUID database column represented in a query through a text input field.

  • Steps: tried the {{!id.value}} OR id = {{id.value}}::uuid value approach that works with text/integer columns and text/integer input field types, with and without ::uuid cast, changing the filter {{!id.value}} to casting ::uuid, == null, == ""....

  • Details: the query is rather simple, but I keep getting the following error for almost everything: invalid input syntax for type uuid: ""

Query:

SELECT * FROM table
WHERE {{!id.value}} OR id = {{id.value}}::uuid

Given the seemingly straightforward nature of this query, I'm not sure what I might be overlooking.

2

Hello @bass,

The issue here might be that you are not checking for some of the cases where id might be null or empty.
Can you try this?

SELECT * 
FROM table
WHERE {{!id.value}} OR ({{id.value}} IS NOT NULL AND {{id.value}} <> '' AND id = {{id.value}}::uuid)
3

Hi @GuilhermeSilv, thanks for your response. The id column has a NOT NULL constraint and is auto-generated, so there's always a UUID value. Your query does not work, and it produces the following error: could not determine data type of parameter $2

4

What about {{!id.value}} OR id = {{ id.value || null}}::uuid?

1 Like
5

That worked, thanks!

6

Glad to hear!