Drizzle Team•8mo ago

How can I convert a null value to integer in a select?

I am doing a left join so joined table may contain null fields. In this case I have a player.ranking field that if null I want to default to 9999. I've tried numerous ways but nothing is working:

  const data = await db.select({
    player_name: tournament_snapshot_detail.player_name,
    ranking: sql<number>`ISNULL(${player.ranking},9999)`
  }).from(tournament_snapshot_detail)
    .leftJoin(player, eq(tournament_snapshot_detail.player_name, player.name))
    .where(and(
      eq(tournament_snapshot_detail.snapshot_id, snapshots[0].value ?? 0),
      eq(tournament_snapshot_detail.amateur, false)))
    .orderBy(player.ranking)

  const data = await db.select({
    player_name: tournament_snapshot_detail.player_name,
    ranking: sql<number>`ISNULL(${player.ranking},9999)`
  }).from(tournament_snapshot_detail)
    .leftJoin(player, eq(tournament_snapshot_detail.player_name, player.name))
    .where(and(
      eq(tournament_snapshot_detail.snapshot_id, snapshots[0].value ?? 0),
      eq(tournament_snapshot_detail.amateur, false)))
    .orderBy(player.ranking)

Solution:

I just got it .. keep forgetting postgres does some things I'm not used to:

ranking: sql<number>`coalesce(${player.ranking},9999)

ranking: sql<number>`coalesce(${player.ranking},9999)

`...

Jump to solution

2 Replies

Richard EOP•8mo ago

I have also tried the following:

ranking: player.ranking??9999

ranking: player.ranking??9999

Solution

Richard E•8mo ago

I just got it .. keep forgetting postgres does some things I'm not used to:

ranking: sql<number>`coalesce(${player.ranking},9999)

ranking: sql<number>`coalesce(${player.ranking},9999)

Gaming

Programming

How can I convert a null value to integer in a select?