Coalesce return empty array

Dddanielcruzz5/19/2023
Hey is there a way to return an empty array rather than null when an array column is empty?

I already tried
.select(coalesce("categories",sql`ARRAY[]::"CollectionCategory"[]`).as("categories"))


but it's still returning null and not an empty array
IIgal5/19/2023
Hey 👋

This should work:

.select(({ fn }) => fn.coalesce(fn<Category[]>('to_json', ['categories']), sql<Category[]>`'[]'`).as('categories'))
Dddanielcruzz5/19/2023
I get

malformed array literal: "[]"
Dddanielcruzz5/19/2023
I think it may be a node-postgres thing? That empty sql arrays are transformed to null ?
IIgal5/19/2023
edited my snippet
IIgal5/19/2023
not sure, but you can control it via pg-types
Dddanielcruzz5/19/2023
still null 😦
Dddanielcruzz5/19/2023
I guess I'll just handle the null. I'm migrating from Prisma and out of nowhere my app crashed haha, and it's because Prisma returns empty array
Dddanielcruzz5/19/2023
I tried casting the whole thing to ::text[] and still got null
IIgal5/19/2023
pg-types is a package
Dddanielcruzz5/19/2023
Oh yeah I had another issue and tried to using it but I have to pass an id for the type, which could be the same or not between dev db and prod
Kkoskimas5/20/2023
Custom enum types seem to cause all kinds of issues in both pg and in Kysely. If it's possible, you could consider dropping your custom types and use text instead. There's at least two enum patterns that I've found working great:

1. Simple text column with a check constraint that limits the value to the enum values.

2. Text column as a foreign key to an enum table. The enum table only has one primary key column and you insert the enum options there. The foreign key ensures the values but you still get a simple text data type. Unfortunately this means that you need a pivot table each time you have an array of enums in a table.