Double Quotes Array in MultiSelect and Postgres Filter

I want to create a MultiSelect Dropdown Menu that filters a table in Retool. I want to filter the Postgres Query witht the Where column IN ({{...}}) statement. Unfortunately the Dropdown (which is also loaded by a database column) returns the array values in the array format with double quotes ["a", "b", "c"] but it seems that postgres has a problem with that and expect a single quote array ['a', 'b', 'c']. Can you help with that ?
Thank you

@Manu what's the exact Postgres error you're getting?

Hi! Resurrecting this because I think I'm having a similar issue. I managed to get single quotes around all my array items, except the whole array is enclosed in double quotes. Is there any way to avoid this? @Justin, to answer the question about postgres error, it actually doesn't error for me, because it's searching (I think?) for a big ole string in the WHERE clause.

I've tried a couple of different methods, passing an array, passing a joined string, with and without parenthesis, etc. I'm guessing I must be making a fundamental mistake cause somewhere..

Edit: solved it by disabling prepared statements.

Opening this again - is there any way to use prepared statements, but have retools interpolations return single quoted strings? Could this be a feature request? I'd like to be able to search by string literals in postgres, but also have some basic protection against SQL injection, which I think can be possible?

Hi @MarkR, Could this syntax from our docs work for you?

Resurrecting this because I'm having a similar issue. The docs @everett_smith linked are helpful and I did give it a shot, but seem to be optimized for numeric types.

In its basic form, I'm attempting a filter based on a MultiSelect. User selects one or many state abbreviations from a list of 50 states. This queries on a column containing state abbreviations. Like Mark, in effect it seems as though this

SELECT DISTINCT party from main where reg_state IN ({{ => `'${d}'`).join(',')}})

is interpreted as:

SELECT DISTINCT party from main where reg_state IN ("'MI','OH','IN'")

So the JS is creating a single double quoted string comprised of single quote encapsulated state abbreviations.

Any suggestions for getting those double quotes to drop out in the returned JS?

(postgres into Redshift)

Hey @redfiel3, Does SELECT DISTINCT party from main where reg_state = ANY ({{multiselect1.value}}) work on your end?


Ahhh it does. Thanks so much.

You bet!

Hi there, I'm having a similar problem.
Trying to implement server-side pagination with PostgreSQL, cannot find a way to implement ordering ASC|DESC, the query fails since asc/desc is getting quoted.


Please help?

Hey @Jose_Luis_Pereira! Hopping in here to try helping, thank you for waiting for quite some time.

This looks like a prepared statements issue, since ASC or DESC are reserved words in SQL. Anytime you try to make a column name or reserved word dynamic, you’ll need to disable prepared statements on your resource.

You could also probably write two separate queries (one for ASC and one for DESC) and then write a JS query to conditionally trigger the one you want.

Let me know if you have any questions about this!