Hi all, I'm trying to use SqlDelight 2.0.0 with Postgres dialect on JVM and trying to write the following query:

selectArticles:
SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
     , COALESCE (array_agg (tags.tag) FILTER (WHERE tags.tag IS NOT NULL), '{}') AS articleTags
FROM articles
LEFT JOIN tags ON articles.id = tags.article_id
JOIN users ON articles.author_id = users.id
GROUP BY articles.id, users.id;

this fails on

:generateMainSqlDelightInterface

with

/home/matobet/projects/kotlin/ktor-arrow-example/src/main/sqldelight/io/github/nomisrev/sqldelight/Articles.sq: (44, 38): ')', <binary like operator real>, BETWEEN, IN or WITHIN expected, got 'FILTER'
43    SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
44         , COALESCE (array_agg (tags.tag) FILTER (WHERE tags.tag IS NOT NULL), '{}') AS articleTags
                                            ^^^^^^
45    FROM articles
46    LEFT JOIN tags ON articles.id = tags.article_id
47    JOIN users ON articles.author_id = users.id
48    GROUP BY articles.id, users.id

When I try to simplify the expression I still get `unknown function array_agg`:

/home/matobet/projects/kotlin/ktor-arrow-example/src/main/sqldelight/io/github/nomisrev/sqldelight/Articles.sq: (43, 114): Unknown function array_agg
43    SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt, array_agg(tags.tag)
                                                                                                                        ^^^^^^^^^^^^^^^^^^^
44    --      , COALESCE (array_agg (tags.tag) FILTER (WHERE tags.tag IS NOT NULL), '{}') AS articleTags
45    FROM articles
46    LEFT JOIN tags ON articles.id = tags.article_id
47    JOIN users ON articles.author_id = users.id
48    GROUP BY articles.id, users.id

Are these things planned to be added to the Postgres dialect or is there some workaround I could use locally in the meantime?

Sadly,

FILTER

is not in the current PostgreSql grammar in sqldelight. As a work-around you could try - Use

CASE WHEN

instead, the old way 🗝️

, COALESCE (array_agg (CASE WHEN tags.tag IS NOT NULL THEN tags.tag END) , '{}') AS articleTags

If you have a function that is not yet implemented you can surround in parentheses 😼

(array_agg(tags.tag))

This does compile anyway with

COALESCE (array_agg..

Raise 🎫 for

FILTER

and

array_agg

https://github.com/cashapp/sqldelight/issues

@griffio thanks! that moved me a bit closer, although for the

COALESCE

column I get generated mapper parameter with type `String`:

public fun <T : Any> selectArticles(mapper: (
    id: ArticleId,
    slug: String,
    title: String,
    description: String,
    body: String,
    username: String,
    bio: String,
    image: String,
    createdAt: OffsetDateTime,
    updatedAt: OffsetDateTime,
    articleTags: String,  <----------
  )

It even "works" at runtime (in a sense it doesn't crash 😅), giving me the textual representation of the array

"{tag1,tag2}"

but is there perhaps a way how to explicitly state the type of that column, so that generated mapper uses

List<String>

or similar?

You can use

tag TEXT AS List<String> NOT NULL

https://cashapp.github.io/sqldelight/2.0.0/jvm_postgresql/types/#custom-column-types Your custom mapper can decode the

"{tag1,tag2}"

that would be for column definition but how does one do that for the

COALESCE

expression within a

SELECT

query?

SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
     , COALESCE (array_agg (CASE WHEN tags.tag IS NOT NULL THEN tags.tag END) , '{}') AS articleTags

Ah Yes - you are right, only the aggregate function should return a

List<String>

, not the the tag column type. I will have to look into it 👓 - the function resolvers are quite limited