K
Join ServerKysely
help
Coalesce return empty array
Hey is there a way to return an empty array rather than
I already tried
but it's still returning null and not an empty array
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:
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 arrayI tried casting the whole thing to
::text[]
and still got nullpg-types
is a packageOh 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
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.
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.