I'm getting a Postgres Error when running my drizzle query but not when running the generated sql

I have a drizzle query.

const staffDateExpression = sql<Date>`date_trunc(${timeFrame}, ${CalendarEvent.startTimeUTC})`;
  // 1. Calculate staffing costs
  const staffingCosts = await ctx.db
    .select({
      date: staffDateExpression.as("date"),
      value: sum(
        sql`(${CalendarEvent.endTimeUTC}::timestamp - ${CalendarEvent.startTimeUTC}::timestamp) * ${ClinicStaffProfile.costPerHour} / 3600`,
      ).as("value"),
    })
    .from(Appointment)
    .innerJoin(CalendarEvent, eq(Appointment.calendarEventId, CalendarEvent.id))
    .innerJoin(
      ClinicStaffProfile,
      eq(Appointment.providerId, ClinicStaffProfile.userId),
    )
    .where(
      and(
        eq(Appointment.clinicId, clinicId),
        between(CalendarEvent.startTimeUTC, startDate, endDate),
        isNotNull(ClinicStaffProfile.costPerHour),
      ),
    )
    .groupBy(staffDateExpression);

const staffDateExpression = sql<Date>`date_trunc(${timeFrame}, ${CalendarEvent.startTimeUTC})`;
  // 1. Calculate staffing costs
  const staffingCosts = await ctx.db
    .select({
      date: staffDateExpression.as("date"),
      value: sum(
        sql`(${CalendarEvent.endTimeUTC}::timestamp - ${CalendarEvent.startTimeUTC}::timestamp) * ${ClinicStaffProfile.costPerHour} / 3600`,
      ).as("value"),
    })
    .from(Appointment)
    .innerJoin(CalendarEvent, eq(Appointment.calendarEventId, CalendarEvent.id))
    .innerJoin(
      ClinicStaffProfile,
      eq(Appointment.providerId, ClinicStaffProfile.userId),
    )
    .where(
      and(
        eq(Appointment.clinicId, clinicId),
        between(CalendarEvent.startTimeUTC, startDate, endDate),
        isNotNull(ClinicStaffProfile.costPerHour),
      ),
    )
    .groupBy(staffDateExpression);

Which generates this query

select date_trunc($1, "calendar_event"."start_time_utc") as "date", sum(("calendar_event"."end_time_utc"::timestamp - "calendar_event"."start_time_utc"::timestamp) * "clinic_staff_profile"."cost_per_hour" / 3600) as "value" from "appointment" inner join "calendar_event" on "appointment"."calendar_event_id" = "calendar_event"."id" inner join "clinic_staff_profile" on "appointment"."provider_id" = "clinic_staff_profile"."user_id" where ("appointment"."clinic_id" = $2 and "calendar_event"."start_time_utc" between $3 and $4 and "clinic_staff_profile"."cost_per_hour" is not null) group by date_trunc($5, "calendar_event"."start_time_utc") -- params: ["day", "itt4i7h048d4xllh185k4txi", "2024-06-23T19:05:11.869Z", "2024-07-23T19:05:11.870Z", "day"]

select date_trunc($1, "calendar_event"."start_time_utc") as "date", sum(("calendar_event"."end_time_utc"::timestamp - "calendar_event"."start_time_utc"::timestamp) * "clinic_staff_profile"."cost_per_hour" / 3600) as "value" from "appointment" inner join "calendar_event" on "appointment"."calendar_event_id" = "calendar_event"."id" inner join "clinic_staff_profile" on "appointment"."provider_id" = "clinic_staff_profile"."user_id" where ("appointment"."clinic_id" = $2 and "calendar_event"."start_time_utc" between $3 and $4 and "clinic_staff_profile"."cost_per_hour" is not null) group by date_trunc($5, "calendar_event"."start_time_utc") -- params: ["day", "itt4i7h048d4xllh185k4txi", "2024-06-23T19:05:11.869Z", "2024-07-23T19:05:11.870Z", "day"]

When I run my js code I get the error

PostgresError: column "calendar_event.start_time_utc" must appear in the GROUP BY clause or be used in an aggregate function

but when I just run the query in Dbeaver it runs just fine. I'm very confused. I'm not sure if I made a mistake or if this is a bug. Seems like a bug though.

1 Reply

G$RowOP•6mo ago

Ok so it seems that having the time frame be put in the sql string staffDateExpression is where the problem comes from. It doesn't recognize that they are the same for some reason. Not sure why I figured out a way around it.

  switch (timeFrame) {
    case "day":
      dateExpression = sql`DATE(${Appointment.checkedOutAt})`;
      break;
    case "week":
      dateExpression = sql`DATE_TRUNC('week', ${Appointment.checkedOutAt}) `;
      break;
    case "month":
      dateExpression = sql`DATE_TRUNC('month', ${Appointment.checkedOutAt}) `;
      break;
    case "year":
      dateExpression = sql`DATE_TRUNC('year', ${Appointment.checkedOutAt}) `;
      break;
  }

  switch (timeFrame) {
    case "day":
      dateExpression = sql`DATE(${Appointment.checkedOutAt})`;
      break;
    case "week":
      dateExpression = sql`DATE_TRUNC('week', ${Appointment.checkedOutAt}) `;
      break;
    case "month":
      dateExpression = sql`DATE_TRUNC('month', ${Appointment.checkedOutAt}) `;
      break;
    case "year":
      dateExpression = sql`DATE_TRUNC('year', ${Appointment.checkedOutAt}) `;
      break;
  }

If I use a switch statement and not pass in the timeFrame as a parmeter it works. I'm not sure if this problem is a bug in the driver or in drizzle

Gaming

Programming

I'm getting a Postgres Error when running my drizzle query but not when running the generated sql