Cody Oakes Comments - Answer Overflow

Cody Oakes

•Created by Cody Oakes on 1/7/2025 in #help

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

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!

14 replies

DTDrizzle Team

•Created by Cody Oakes on 1/7/2025 in #help

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

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

14 replies

DTDrizzle Team

•Created by Cody Oakes on 1/7/2025 in #help

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

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?

14 replies

DTDrizzle Team

•Created by Cody Oakes on 1/7/2025 in #help

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

Nice that worked!

14 replies

DTDrizzle Team

•Created by Cody Oakes on 1/7/2025 in #help

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

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,

);

14 replies

DTDrizzle Team

•Created by Cody Oakes on 1/7/2025 in #help

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

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

14 replies

Gaming

Programming