Postgres with sqlFile - the operator ? becomes?

General Discussion
1

I came across a mystery today. Have been using Liquibase for a few months in a pre-production context, and just ran into this issue.

I’m seeing source code that uses the “?” operator in postgres getting created in the database with that question mark repeated.

for example, this source code
WHERE f.filter_object::JSONB ? 'patient_status';

appears like this when I view the SQL in pgAdmin
WHERE f.filter_object::JSONB ?? 'patient_status';

Is there some setting that may be controlling this translation?
For some context, our changesets use the sqlFile tag, e.g.

<changeSet author="alexey" id="patient_search.test.sql" runOnChange="true">
   <sqlFile dbms="postgresql" endDelimiter=";" path="../../functions/patient_search.test.sql" relativeToChangelogFile="true" splitStatements="false" stripComments="false"/>
 </changeSet>
1 Like
2

Hi @rsredsail ,

I am not a Postgres sql expert and never needed to use the ? operator but was wondering if you could tell me:

  • What error are you getting? I understand there is an extra ? but is liquibase executing the changeset in an unexpected way?
  • What version of Liquibase are you using?

I did find some articles outlining issues using ? in Postgres, perhaps these will help, a bit out of my depth tho: here and here.

1 Like
3

I was having the exact same problem and I resolved using an old version of liquibase (3.6.1 in my case). We tried 4.2.2 and 4.3.2 and both don’t work so try rolling back to an older version.

Hope this helps :smiley:

1 Like
4

@rsredsail I have same issue last few days to see. But now i completely solve it.

5

Thanks for doing this :slightly_smiling_face: :slightly_smiling_face: :slightly_smiling_face: :

6

The version of Liquibase I was using when I reported this was 4.2.2 #36
I’ve worked around the issue to not use the ? operator, but if I need to, I’ll try an older version.

To answer @ronak’s question, Liquibase was executing the changeset normally, except it was just adding the extra ?. When attempting to run the function, Postgres would complain about the extra ? – unknown operator, I think was the message.