CODEWITHSUNDEEP
pythonsqldjangopostgresql
Patryk
Patryk

Reputation: 23

Postgres: Could not choose a best candidate function

Can someone explain how to fix this query?

SELECT date_part('month', scheduler_scheduleevents.date), sum(price)
    FROM user_settings_userservices
    JOIN scheduler_scheduleevents
    ON scheduler_scheduleevents.service_type_id = user_settings_userservices.id
    WHERE user_settings_userservices.salonid_id = %s
    AND is_start_time = True and is_active = False
    AND ( date < %s or ( date = %s and time < %s ) )
    AND date_part('year', scheduler_scheduleevents.date) = date_part('year', %s)
    GROUP BY date_part('month', scheduler_scheduleevents.date),
    (request.user.id, now_date, now_date, now_time, now_date, )
    )

When I try execute this query in django app I get this warning:

function date_part(unknown, unknown) is not unique

LINE 9: ...ate_part('year', scheduler_scheduleevents.date) = date_part(...
                                                         ^

HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Upvotes: 1

Views: 6799

Answers (1)

Roberto
Roberto

Reputation: 2185

You should add an explicit cast:

AND date_part('year', scheduler_scheduleevents.date::date) = date_part('year', %s::date)

You get this error when postgres can't figure out which actual function execute.

Postgres has the date_part function overloaded, it has a version for a timestamp as second parameter, and another for an interval second parameter: date_part(text, timestamp) and date_part(text, interval).

When you say date_part('year', %s) postgres doesn't know if it should interprete your %s parameter as a timestamp or as a interval. So it needs you to cast your %s parameter to a type that only matches one of those two versions of the date_part function. When you cast %s to date (i.e. when you write %s::date) postgres can only use the first overload of the function, because a date can be casted automatically to timestamp but can't be casted automatically to interval

Upvotes: 7

Related Questions