Question mark being interpreted as a parameter in PostgreSQL statement

 

I am writing a query using a database select component. The query involves joining a varchar column with jsonb column.

 

Unlike the syntax for usual join (col1 =  col2), the postgresql syntax for this join is ( jsonbcol1 ? col2).   

 

While this syntax works in any other command line tool, pipeline pilot SQLSelect component interprets that question mark as an input parameter.  I tried passing "?" itself as the parameter value hoping that it will replace question mark with question mark in the process but that did not work.

 

Any ideas how to work around this issue?

Answers 5
AC
AC 2025-02-11

Hi @SC. This might require an answer from someone like @CB, who has more experience with our SQL component implementation. The '?' is a placeholder for a parameter in a PreparedStatement according to the SQL specification. The problem is that the PostgreSQL JDBC implementation uses the '?' as an operator. I've seen mentions in the PostgreSQL documentation that in later versions of their JDBC driver, a double question mark (??) can be used instead of a single question mark, but that doesn't seem to work for me, which may be because I have an older version of the driver. The only thing that I could get to work and not give an error is replacing the ? with '|| chr(63) ||' which was suggested in this post.

JF
JF 2025-02-11

I can think of a clunky suggestion which might work...  Could you could set a pilotscript global @question_mark='?' and then use $(question_mark) inside the query?

SC
SC 2025-02-11

Thanks John, but that did not work. Was good idea though.

SB
SB 2025-02-12

It sounds like this is a general postgres issue when dealing with prepared statements.  The preferred answer to a stackoverflow question on this (https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb) recommends avoiding the operator entirely and using a built-in function instead and gives suggestions on how to do so.  (It also recommends using a static statement instead of a prepared statement, but that isn't an option for Pipeline Pilot).  The linked answer (https://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes/) also recommends the double question mark (for postgres drivers later than about 6 years ago).  Pipeline Pilot processes the parameter mapping within the sql driver, so the solution needs to come from the driver.

SC
SC 2025-02-12

I used lateral join with the table to unpivot the values in jsonb column and then join with the other table with varchar column. No question mark needed any more.