operator does not exist: interval < date

Question

I want to do the following comparison :

IF ( V_C_CRITERE2.DATE_FIN_SOUHAITEE - TO_DATE(V_SEUIL_ALERT) < CURRENT_DATE) THEN

DATE_FIN_SOUHAITEE is a DATE.

But a get this error :

operator does not exist: interval < date 

How can i do the comparison?

Answer 1

Although the question states that DATE_FIN_SOUHAITEE is a DATE, the error message "operator does not exist: timestamp without time zone - numeric" makes it clear that it really is a timestamp. In Postgres - unlike Oracle - a timestamp is something substantially different than a date (in Oracle they are more or less the same thing - they just differ in the precision).

In Postgres you can only subtract an interval from a timestamp, and you can subtract integer values from a date

If V_SEUIL_ALERT is indeed a varchar that stores a number that represents years, you need to convert that string value to a proper interval in order to be able to subtract it from a timestamp:

V_C_CRITERE2.DATE_FIN_SOUHAITEE - make_interval(years => V_SEUIL_ALERT::integer)

the above can then be compared using < with current_date.

I don't know what the original code in Oracle did, because DATE_FIN_SOUHAITEE - V_SEUIL_ALERT would implicitly convert V_SEUIL_ALERT to a number which is then taken as days to be subtracted from DATE_FIN_SOUHAITEE, not years.

If that column indeed stores days rather than years (as stated in the comments), you need to use:

make_interval(days => V_SEUIL_ALERT::integer)

---

You should really take the opportunity of that migration and fix the wrong data type for the column V_SEUIL_ALERT to be an integer, numeric or even interval if that is what you use it for. Never store numbers in VARCHAR columns. That was already a really, really bad idea in Oracle.