operator does not exist: interval < date
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 =&gt; V_SEUIL_ALERT::integer)


the above can then be compared using &lt; 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 =&gt; 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.