Question about using Option/NULL in `where`?

[pbiggar]

I just wrote the code below, and it feels wrong but I couldn't find another way that would work.

I did this because = NULL doesn't work in postgres, so the Sql.timezonetzOrNone option was failure.

Is there an idiomatic approach using the OrNone variety of parameters?

    let (equality, currentLockedAt) =
      match event.lockedAt with
      | None -> "IS", SqlValue.Null
      | Some instant -> "=", Sql.timezonetz instant

    let! rowCount =
      Sql.query
        $"UPDATE events_v2
            SET locked_at = CURRENT_TIMESTAMP
          WHERE id = @eventID
            AND canvas_id = @canvasID
            AND locked_at {equality} @currentLockedAt"
      |> Sql.parameters [ "eventID", Sql.uuid event.id
                          "canvasID", Sql.uuid event.canvasID
                          "currentLockedAt", currentLockedAt ]
      |> Sql.executeNonQueryAsync

[Zaid-Ajaj]

Hi @pbiggar, am I correct that the following doesn't work for you?

    let! rowCount =
      Sql.query
        $"UPDATE events_v2
            SET locked_at = CURRENT_TIMESTAMP
          WHERE id = @eventID
            AND canvas_id = @canvasID
            AND locked_at = @currentLockedAt"
      |> Sql.parameters [ "eventID", Sql.uuid event.id
                          "canvasID", Sql.uuid event.canvasID
                          "currentLockedAt", Sql.timezonetzOrNone event.lockedAt ]
      |> Sql.executeNonQueryAsync

[pbiggar]

Yes, that's right - that was my initial attempt and it took me a while to figure out that = null doesn't match any rows in postgres.

I actually ended up with the following, as my initial code was broken:

    let (querySegment, args) =
      match event.lockedAt with
      | None -> "IS NULL", []
      | Some instant ->
        "= @currentLockedAt", [ "currentLockedAt", Sql.timezonetz instant ]
    let! rowCount =
      Sql.query
        $"UPDATE events_v2
            SET locked_at = CURRENT_TIMESTAMP
          WHERE id = @eventID
            AND canvas_id = @canvasID
            AND locked_at {querySegment}"
      |> Sql.parameters (
        [ "eventID", Sql.uuid event.id; "canvasID", Sql.uuid event.canvasID ] @ args
      )
      |> Sql.executeNonQueryAsync

[pbiggar]

OK, looks like postgres has a way to do equality here: IS NOT DISTINCT FROM. Today I learned. It may be worth mentioning it next to the OrNone parameters in the docs.

    let! rowCount =
      Sql.query
        $"UPDATE events_v2
            SET locked_at = CURRENT_TIMESTAMP
          WHERE id = @eventID
            AND canvas_id = @canvasID
            AND locked_at IS NOT DISTINCT FROM @currentLockedAt"
      |> Sql.parameters [ "eventID", Sql.uuid event.id
                          "canvasID", Sql.uuid event.canvasID
                          "currentLockedAt",
                          Sql.timestamptzOrNone event.lockedAt ]