i’m having trouble with two postgres queries in sq...

# squarelibraries

[William Reed]

i’m having trouble with two postgres queries in sqldelight 2.0.2

1. I have table business with a column locations that is a TEXT[]. This query runs fine in postgres but in sqldelight gives me ')', ',', '.', <compound operator real>, <join operator real>, <table alias real>, AS, FOR, GROUP, HAVING, INDEXED, LIMIT, NOT, OFFSET, ORDER or WHERE expected, got '('

SELECT *
FROM business
WHERE EXISTS (
    SELECT 1
    FROM unnest(locations) as loc
    WHERE lower(loc) LIKE '%' || LOWER(:query) || '%'
)

2. with the same schema the following query gives me error '(', <bind parameter real>, <column expr real>, <expr>, <extension expr real>, <literal value real>, EXISTS, RAISE or id expected, got 'EXISTS' which also runs fun in postgres

getAllBusinessesMatchingQuery:
SELECT *
FROM business
WHERE LOWER(name) LIKE '%' || LOWER(:query) || '%'
OR EXISTS (
    SELECT 1
    FROM unnest(locations) as loc
    WHERE lower(loc) LIKE '%' || LOWER(:query) || '%'
)

I can create bug tickets for these, just want to make sure it’s not something i’m doing wrong first.

[Chrimaeon]

I guess you set the dialect in the build file, right?

https://cashapp.github.io/sqldelight/2.0.2/jvm_postgresql/

[William Reed]

yes

[hfhbd]

It’s not yet supported.

[hfhbd]

Create a ticket please (or a pr too ;)

[William Reed]

tickets made
https://github.com/cashapp/sqldelight/issues/5347
https://github.com/cashapp/sqldelight/issues/5346