sql statement with EXTRACT(DAY from appointment_timestamp) = @dow generates params code where dow is of type time.Time and not int

[hareldvd]

My apologies about the mess I am going to make here. I am new to sqlc on go.
For my defense I tried to search for issues with "extract(day" before I opened this discussion
AI (Gemini) advised me to show table definition from schema block:
I tried to highlight the relevant parts.

-- public.treatment_template definition

-- Drop table

-- DROP TABLE public.treatment_template;

CREATE TABLE public.treatment_template (
customer_id int4 NOT NULL,
patient_ttz varchar(20) NOT NULL,
health_insurance_id int4 NULL,
customer_institute_id int4 NULL,
appointment_timestamp timestamp NOT NULL,
duration int4 NULL,
arrival_status int4 NULL,
tariff_id int4 NULL,
cancellation_timestamp timestamp NULL,
cancellation_tariff_id int4 NULL,
foreman_id int4 NULL,
therapist_id int4 NOT NULL,
is_stagiaire int4 NULL,
treatment_plan_id int4 NULL,
treatment_summary text NULL,
treatment_remark text NULL,
fee float8 NULL,
status int4 NULL,
update_user_id int4 NULL,
update_timestamp timestamp NULL DEFAULT now(),
CONSTRAINT treatment_template_pk PRIMARY KEY (customer_id, patient_ttz, therapist_id, appointment_timestamp)
);

The query:
-- name: GetEndedTariffForTemplate :many
select --distinct on (ttmp.tariff_id)
EXTRACT(DAY from ttmp.appointment_timestamp) as dow
, ttmp.appointment_timestamp
, coalesce(ttmp.tariff_id, 0)
, coalesce(trf.tariff_name, '')
, ttmp.patient_ttz
, coalesce(pa.first_name, '') || ' ' || coalesce(pa.last_name, '') as patient_name
, coalesce(ui.first_name, '') || ' ' || coalesce(ui.last_name, '') as therapist_name
from public.treatment_template ttmp
join public.patient pa using(customer_id, patient_ttz)
left join public.tariff trf using (customer_id, tariff_id)
join public.user_info ui on ui.customer_id = ttmp.customer_id and ui.user_id = ttmp.therapist_id
where ttmp.customer_id = @customer_id
and case when @Dow > 0 then EXTRACT(DAY from ttmp.appointment_timestamp) = (@Dow -1) else true end
and (trf.status = 0 or trf.status is null)
and ttmp.status = 1
order by ttmp.appointment_timestamp
;

and the compiled/auto generated file (calendar.sql.go )

const getEndedTariffForTemplate = -- name: GetEndedTariffForTemplate :many select --distinct on (ttmp.tariff_id) EXTRACT(DAY from ttmp.appointment_timestamp) as dow , ttmp.appointment_timestamp , coalesce(ttmp.tariff_id, 0) , coalesce(trf.tariff_name, '') , ttmp.patient_ttz , coalesce(pa.first_name, '') || ' ' || coalesce(pa.last_name, '') as patient_name , coalesce(ui.first_name, '') || ' ' || coalesce(ui.last_name, '') as therapist_name from public.treatment_template ttmp join public.patient pa using(customer_id, patient_ttz) left join public.tariff trf using (customer_id, tariff_id) join public.user_info ui on ui.customer_id = ttmp.customer_id and ui.user_id = ttmp.therapist_id where ttmp.customer_id = $1 and case when $2 > 0 then EXTRACT(DAY from ttmp.appointment_timestamp) = ($3-1) else true end and (trf.status = 0 or trf.status is null) and ttmp.status = 1 order by ttmp.appointment_timestamp

type GetEndedTariffForTemplateParams struct {
CustomerID int32 json:"customer_id"
Dow time.Time json:"dow"
}

type GetEndedTariffForTemplateRow struct {
Dow string json:"dow"
AppointmentTimestamp time.Time json:"appointment_timestamp"
TariffID int32 json:"tariff_id"
TariffName string json:"tariff_name"
PatientTtz string json:"patient_ttz"
PatientName interface{} json:"patient_name"
TherapistName interface{} json:"therapist_name"
}

func (q *Queries) GetEndedTariffForTemplate(ctx context.Context, arg GetEndedTariffForTemplateParams) ([]GetEndedTariffForTemplateRow, error) {
rows, err := q.db.QueryContext(ctx, getEndedTariffForTemplate, arg.CustomerID, arg.Dow, arg.Dow)
if err != nil {
return nil, err
}
defer rows.Close()
items := []GetEndedTariffForTemplateRow{}
for rows.Next() {
var i GetEndedTariffForTemplateRow
if err := rows.Scan(
&i.Dow,
&i.AppointmentTimestamp,
&i.TariffID,
&i.TariffName,
&i.PatientTtz,
&i.PatientName,
&i.TherapistName,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}

I hope I did not cause too much trouble.

Thanks.

[HongTin2104]

No trouble at all. sqlc infers a parameter's type from the expression around it, and @dow here sits next to EXTRACT(DAY from <timestamp>), so the inference goes wrong. The usual fix is to give the parameter an explicit type with a cast. Because @dow appears twice and sqlc maps both to the same field, cast it in both places:

where ttmp.customer_id = @customer_id
  and case when sqlc.arg(dow)::int > 0
           then EXTRACT(DAY from ttmp.appointment_timestamp) = (sqlc.arg(dow)::int - 1)
           else true end

@dow::int works too; sqlc.arg(dow)::int is the same thing written out. After sqlc generate the field should be Dow int32. This is the pattern in the sqlc docs on naming parameters, which use sqlc.arg(...)::type for the same reason.

Two related things in this query:

• The result column dow comes out as string because EXTRACT returns numeric in PostgreSQL, and sqlc maps numeric to string with database/sql. Cast it if you want an int: EXTRACT(DOW from ttmp.appointment_timestamp)::int as dow.
• DAY is the day of the month (1–31), not the day of the week. Since you named it dow, you probably want EXTRACT(DOW from ...) (0 = Sunday … 6 = Saturday) or EXTRACT(ISODOW from ...) (1 = Monday … 7 = Sunday). With ISODOW, @dow can map directly to 1–7, without the - 1.

A tidier version of the filter, if 0 means "any day":

and (sqlc.arg(dow)::int = 0 or EXTRACT(ISODOW from ttmp.appointment_timestamp) = sqlc.arg(dow)::int)

[hareldvd]

Thank you Mr. Nguyen Hong Tin for your patience and support. This one is new to me and it worked: sqlc.arg(dow)::int However, when trying casting using @Dow::int like in: and ***@***.***::int = 0 or EXTRACT(DAY from ttmp.appointment_timestamp) = ***@***.***::int -1)) (line 354) I get an error which has nothing to do with the current query and seem like a bug... sql/queries/calendar.sql:40:11: edited query syntax is invalid: syntax error at or near "d" code around line 40 (just in case...) -- name: PutConflictAlerts :exec INSERT INTO public.alerts( customer_id , customer_institute_id * , alert_type -- line 40* , message , details , initiator_user_id , show_date , status ) VALUES( @customer_id , @customer_institute_id::int , 'alert' , @message::text , @details::text , @user_id::int , ***@***.***_date::text)::date , 1 );

…

On Fri, Oct 30, 2026 at 3:17 PM Nguyen Hong Tin ***@***.***> wrote: No trouble at all. sqlc infers a parameter's type from the expression around it, and @Dow here sits next to EXTRACT(DAY from <timestamp>), so the inference goes wrong. The usual fix is to give the parameter an explicit type with a cast. Because @Dow appears twice and sqlc maps both to the same field, cast it in both places: where ttmp.customer_id = @customer_id and case when sqlc.arg(dow)::int > 0 then EXTRACT(DAY from ttmp.appointment_timestamp) = (sqlc.arg(dow)::int - 1) else true end @Dow::int works too; sqlc.arg(dow)::int is the same thing written out. After sqlc generate the field should be Dow int32. This is the pattern in the sqlc docs on naming parameters <https://docs.sqlc.dev/en/latest/howto/named_parameters.html>, which use sqlc.arg(...)::type for the same reason. Two related things in this query: - *The result column dow comes out as string* because EXTRACT returns numeric in PostgreSQL, and sqlc maps numeric to string with database/sql. Cast it if you want an int: EXTRACT(DOW from ttmp.appointment_timestamp)::int as dow. - *DAY is the day of the month (1–31), not the day of the week.* Since you named it dow, you probably want EXTRACT(DOW from ...) (0 = Sunday … 6 = Saturday) or EXTRACT(ISODOW from ...) (1 = Monday … 7 = Sunday). With ISODOW, @Dow can map directly to 1–7, without the - 1. A tidier version of the filter, if 0 means "any day": and (sqlc.arg(dow)::int = 0 or EXTRACT(ISODOW from ttmp.appointment_timestamp) = sqlc.arg(dow)::int) — Reply to this email directly, view it on GitHub <#4671?email_source=notifications&email_token=BARBYUDKDKM6VEDFR4U5VCL5WSIFBA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOJSHE2DANRRUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVRTG633UMVZF6Y3MNFRWW#discussioncomment-19294061>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BARBYUH47OQNJGYNEFA7U2T5WSIFBAVCNFSNUABIKJSXA33TNF2G64TZHMYTSMZRGYYDMNZZHNCGS43DOVZXG2LPNY5TCMJRHA3DCOJTUF3AE> . Triage notifications, keep track of coding agent tasks and review pull requests on the go with GitHub Mobile for iOS <https://github.com/notifications/mobile/ios/BARBYUDZKYPP3PABUPPYCH35WSIFBA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOJSHE2DANRRUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVJTG633UMVZF62LPOM> and Android <https://github.com/notifications/mobile/android/BARBYUDRGAEZIUGZKDUP7H35WSIFBA5CNFSNUABIM5UWIORPF5TWS5BNNB2WEL2ENFZWG5LTONUW63SDN5WW2ZLOOQXTCOJSHE2DANRRUZZGKYLTN5XKMYLVORUG64VFMV3GK3TUVZTG633UMVZF6YLOMRZG62LE>. Download it today! You are receiving this because you authored the thread.Message ID: ***@***.***>

-- דוד הראל עמוקה ד.נ. מרום הגליל 1380200 טל: 054-2263892