Subtracting numeric from date in Postgresql-CodePudding

When I execute these script, it works in postgresql;

select '2021-09-01'::Date - 1

select current_date -1

But when I use this type of subtracting in function, postgresql gives me error like this;

Function usage in declare part :

v_date date := p_date - p_number;
ERROR:  operator does not exist: date - numeric

CodePudding user response：

Try:

v_date date := p_date - p_number::int;.

Per Date/Time Operators you can only subtract an integer, interval or date from a date.

Note: the ::int cast will round the numeric number.

UPDATE

Another option is to make p_number be an integer from the start, though this depends on whether it is being used for other purposes where it needs to be numeric.