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
but it's still returning null and not an empty array8 Replies
Hey 👋
This should work:
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-typesstill 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 nullpg-types is a packagenpm
pg-types
Query result type converters for node-postgres. Latest version: 4.0.1, last published: 3 months ago. Start using pg-types in your project by running
npm i pg-types. There are 67 other projects in the npm registry using pg-types.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.