Is there a way to include an assertion about the expected number of rows? I have a function that abstracts updating event data:

async (
  connection: DatabaseConnectionType,
  id: number,
  venueId: number,
  movieId: number,
  cinemaMovieNameId: number,
  date: string,
  time: string,
  url: string | null,
  seatingIsAllocated: boolean | null,
  reservationFee: number | null,
  reservationPerTicketFee: number | null,
  guide: $PropertyType&lt;EventResultType, 'guide'&gt; | null
): Promise&lt;number&gt; =&gt; {
  const timezone = await connection.maybeOneFirst(sql`
    SELECT
      locality.timezone
    FROM venue
    INNER JOIN locality
    ON locality.id = venue.locality_id
    WHERE venue.id = ${venueId}
  `);

  if (!timezone) {
    log.error({
      venueId
    }, 'timezone not found');

    throw new Error('Timezone not found.');
  }

  // @todo Prevent unncessary updates. See updateVenue and updateTicket.
  await connection
    .query(sql`
      UPDATE
        event
      SET
        venue_id = ${venueId},
        movie_id = ${movieId},
        cinema_movie_name_id = ${cinemaMovieNameId},
        starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE (${timezone}),
        url = ${url},
        seating_is_allocated = ${seatingIsAllocated},
        reservation_fee = ${reservationFee},
        reservation_per_ticket_fee = ${reservationPerTicketFee},
        guide = ${guide ? JSON.stringify(guide) : null}
      WHERE
        id = ${id}
    `);

  return id;
};


It seems redundant separate locality.timezone lookup query from the UPDATE query. However, combining these two queries requires asserting within a query that timezone exists.

Is there a syntax to assert how many results a query returns?

Something that would allow me to rewrite the above into ~:

await connection
  .query(sql`
    WITH timezone AS (
      SELECT
        locality.timezone
      FROM venue
      INNER JOIN locality
      ON locality.id = venue.locality_id
      WHERE venue.id = ${venueId}
      -- Made up syntax
      ASSERT ROW COUNT 1
    )
    UPDATE
      event
    SET
      venue_id = ${venueId},
      movie_id = ${movieId},
      cinema_movie_name_id = ${cinemaMovieNameId},
      starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE ((SELECT timezone FROM timezone)),
      url = ${url},
      seating_is_allocated = ${seatingIsAllocated},
      reservation_fee = ${reservationFee},
      reservation_per_ticket_fee = ${reservationPerTicketFee},
      guide = ${guide ? JSON.stringify(guide) : null}
    WHERE
      id = ${id}
  `);
It is not an assert, but you can just have the query return the empty string (rather than NULL) when there is no match:

... AT TIME ZONE ((SELECT timezone FROM timezone union select '' limit 1))


when invoked with empty timezone query gives:

ERROR:  time zone "" not recognized