Coalesce return empty array

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

Hey 👋

This should work:

.select(({ fn }) => fn.coalesce(fn<Category[]>('to_json', ['categories']), sql<Category[]>`'[]'`).as('categories'))

I get

malformed array literal: "[]"

I think it may be a node-postgres thing? That empty sql arrays are transformed to null ?

edited my snippet

not sure, but you can control it via pg-types

still null 😦

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

I tried casting the whole thing to ::text[] and still got null

pg-types is a package

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

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.