frankylee
DTDrizzle Team
•Created by frankylee on 7/24/2023 in #help
Cannot create custom `sql` query with casted array of uuid values in Postgres
A necessary use case is to search a column of Postgres type
uuid[] by comparing against a dynamically provided array of values. Due to the way Drizzle escapes quotations in the parameter input, I am unable to get this to work with more than one value. I have tried using the Drizzle inArray() helper, supplying a custom sql query in db.execute(), supplying the sql in the db.select().from(tableSchema).when(), and using the PgDialect class to construct the query.
If I write the query in my Postgres GUI, I am able to query successfully. The problem seems to be around providing Drizzle the dynamic array of values and the way it is constructed in the query.
The Postgres query I need to construct:
What I end up getting in the Drizzle logging is some variation of the following:
This is the closest I have gotten, after having tried numerous variations of constructing the sql string as to manipulate where the Drizzle double quotations would be placed. The other common error I see is:
Any insight would be highly appreciated. Thank you so much!4 replies