Drizzle TeamDT
Drizzle Team•12mo ago
Cody Oakes

Generated Postgres Column adding together other columns that may have null values

This code works until one of the numeric values is null, then the totalQuantity becomes null as well. Is there a way to do this in a drizzle schema that would account for the potential nulls? 

const supplyItem = pgTable('supply_item', {
  id: bigint('id', { mode: 'number' }).primaryKey().generatedAlwaysAsIdentity(),
  name: text('name').notNull(),
  location1name: text('location1_name'),
  location1quantity: numeric('location1_quantity'),
  location2name: text('location2_name'),
  location2quantity: numeric('location2_quantity'),
  location3name: text('location3_name'),
  location3quantity: numeric('location3_quantity'),
  totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`${supplyItem.location1quantity}+${supplyItem.location2quantity}+${supplyItem.location3quantity}`,
  ),
})
Solution
Want to try something. In the generated always as expression, remove the table name before each column name ("supply_item"."location1_quantity" -> "location1_quantity")
Jump to solution
Was this page helpful?