Drizzle Team•2w ago

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}`,
  ),
})

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

8 Replies

Mario564•2w ago

@Cody Oakes You can use Postgres' coalesce function, which takes the last non-null value in a set that you provide:

sql`coalesce(${table.field}, 0 /* 0 if table.field is null. You can set this to whatever you want, even another field */)`;

sql`coalesce(${table.field}, 0 /* 0 if table.field is null. You can set this to whatever you want, even another field */)`;

Cody OakesOP•2w ago

@Mario564 Thanks for the quick reply! I attempted Postgres' coalesce function for the total_quantity field but if one of the supplyItem.locationXquantity values is null the total_quantity values ends up being null as well...am I missing something in the syntax?

totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`coalesce(${supplyItem.location1quantity}, 0)+
      coalesce(${supplyItem.location2quantity}, 0)+
      coalesce(${supplyItem.location3quantity}, 0)`,
  )

totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`coalesce(${supplyItem.location1quantity}, 0)+
      coalesce(${supplyItem.location2quantity}, 0)+
      coalesce(${supplyItem.location3quantity}, 0)`,
  )

Mario564•2w ago

Looks like it's written correctly How does the generated migration look like after this change?

Cody OakesOP•2w ago

CREATE TABLE IF NOT EXISTS "supply_item" (
    "id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "supply_item_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1),
    "name" text NOT NULL,
    "location1_name" text,
    "location1_quantity" numeric,
    "location2_name" text,
    "location2_quantity" numeric,
    "location3_name" text,
    "location3_quantity" numeric,
    "total_quantity" numeric GENERATED ALWAYS AS (coalesce("supply_item"."location1_quantity", 0)+
      coalesce("supply_item"."location2_quantity", 0)+
      coalesce("supply_item"."location3_quantity", 0)) STORED,

);

CREATE TABLE IF NOT EXISTS "supply_item" (
    "id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "supply_item_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1),
    "name" text NOT NULL,
    "location1_name" text,
    "location1_quantity" numeric,
    "location2_name" text,
    "location2_quantity" numeric,
    "location3_name" text,
    "location3_quantity" numeric,
    "total_quantity" numeric GENERATED ALWAYS AS (coalesce("supply_item"."location1_quantity", 0)+
      coalesce("supply_item"."location2_quantity", 0)+
      coalesce("supply_item"."location3_quantity", 0)) STORED,

);

Solution

Mario564•2w ago

Want to try something. In the generated always as expression, remove the table name before each column name ("supply_item"."location1_quantity" -> "location1_quantity")

Cody OakesOP•2w ago

Nice that worked! You think I can remove the table name from the schema file and drizzle will produce the migration that way? Also why doesn't it work with table names? Hmm, looks like I get a type error if I try to change the schema file to :

totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`coalesce(${location1quantity}, 0)+
      coalesce(${location2quantity}, 0)+
      coalesce(${location3quantity}, 0)`,
  )

totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`coalesce(${location1quantity}, 0)+
      coalesce(${location2quantity}, 0)+
      coalesce(${location3quantity}, 0)`,
  )

Mario564•2w ago

You think I can remove the table name from the schema file and drizzle will produce the migration that way?

You can try doing this:

sql`"${sql.raw(table.field.name)}"`

sql`"${sql.raw(table.field.name)}"`

Not as elegant but it does (or at least should) work.

Also why doesn't it work with table names?

Not entirely sure, apparently it's something to do with how Postgres handles references, scopes or something like that

Cody OakesOP•2w ago

gotcha ya with the coalesce ended up looking like

totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`coalesce(${sql.raw('location1_quantity')}, 0)+
      coalesce(${sql.raw('location2_quantity')}, 0)+
      coalesce(${sql.raw('location3_quantity')}, 0)`,
  ),

totalQuantity: numeric('total_quantity').generatedAlwaysAs(
    (): SQL =>
      sql`coalesce(${sql.raw('location1_quantity')}, 0)+
      coalesce(${sql.raw('location2_quantity')}, 0)+
      coalesce(${sql.raw('location3_quantity')}, 0)`,
  ),

@Mario564 thank you for your help!

Gaming

Programming

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

Did you find this page helpful?