How to fix regr_slope() and regr_intercept() returning null PostgreSQL?

Question

I select the sum of values from a table, and them group them by date. This is the result.

CREATE TABLE  thedata
        (
        sum_values INTEGER NOT NULL
        , date DATE NOT NULL
        );
INSERT INTO thedata(sum_values,date) VALUES
 (12       ,'2020-11-20' )
,(12       ,'2020-11-21' )
,(12       ,'2020-11-22' )
,(14       ,'2020-11-23' )
,(15       ,'2020-11-24' )
,(18       ,'2020-11-25' )
,(19       ,'2020-11-26' )

Then I calculate regression slope and interception point with this query:

select date,
        regr_slope(sum_values, extract(epoch from date)),
        regr_intercept(sum_values, extract(epoch from date))
from thedata
group by date
order by date

SQL Fiddle

However, I get NULL values as result. This answer says that it is a version issue. My version is 10.0, so this might indeed be the case, however I can't afford to update to a newer version. How can I fix the slope and intercept calculation?

score 1 · Accepted Answer · answered Feb 11 '21 at 19:34

I just needed to remove date from the select statement, because it was interfering with the functions:

select regr_slope(sum_values, extract(epoch from date)),
       regr_intercept(sum_values, extract(epoch from date))
from thedata


regr_slope              regr_intercept
0.000018696581196581197 -30013.098901098903

How to fix regr_slope() and regr_intercept() returning null PostgreSQL?

1 Answers1