Query works in the query editor but not in Pocket Query

[Joao Flavio Mendonca]

I am running a query with single quotes in the select and it is not working in Pocket Query.

This is the query:

SELECT
LEFT(c.title,(position('.' IN c.title)))||'pdf' as "document",
ct.creationdate "labeldate",
c.pageid "page"
FROM content c

I receive a error in confluence referring to syntax error next the word "IN", however if I run the same query in the database , it works perfectly.

Is there any interpretation error in Pocket Query?

Thanks

Joao Flavio

[Diego · Accepted solution]

Hello there!

João, I can see that this query is not attributing the name ct to any table. This might be te problem that you are facing when you try to run the query.

I tried out this query and the only issue I had running it was with the ct.creationdate part.Since there is no table being "nicknamed" ct, the query was not running. When I changed it the query returned values as expected.

I am running PostgreSQL on my local instance.

So my advice would be to change ct.creationdate to c.creationdate and check again if the query runs! Also, which database are you currently running?

Looking forward to your reply!

[Joao Flavio Mendonca]

Hello Diego, 

Thanks a lot for your answer.

The problem was not in the query itself but the in the database and driver used by Confluence to connect to the database.

After we upgrade the database and correctly setup the driver everything worked again.

Thanks again for your help

Best Regards

Joao Flavio

[Diego]

Hey, great to hear that João!

If you ever need something, come share with the Community.