How to use a state variable in a LIKE postgres query

Queries and Resources
, ,
1

Hi there, I have written the following query

select * from "Pets"
where "Id" like '%{{ petId.value }}%'
and "OwnerId" like '%{{ ownerId.value }}%'

Both petId and ownerId are temporary state variables. The default values for these state variables are just empty strings ("").

When I try to run the query, it gives me the following error.

* error:true
* message:"could not determine data type of parameter $1"
* source:"resource"

Is there something I need to do when trying to use the like query? I feel like theres something about the % character that is throwing the error?

2

Hey @Tamate and welcome to the forums! Yea, it’s mostly likely related to your syntax. Can you try {{ '%' + petId.value + '%' }}? And same with the second one.

3

Ahhh thats solved my issue, thank you so much!

4

@justin in my case if I use this syntax. it will throw a syntax error at or near "$1"

Reference

image
image1533×622 97.4 KB
.

I hope let you come back with solution, it will helpful :person_tipping_hand:

5

Hey @Keshava_Nanda! Are you trying to set a dynamic column name? This may be a prepared statements issue.

https://docs.retool.com/docs/sql-queries#dynamic-table-names

1 Like