Cannot update postgres table from cte, value goes missing

Question

I have a Postgres Timescaledb table (mysolar) in which I collect energyvalues (time,energy) from my solarpanels. Sometimes, for whatever reason, I get a ‘zero-value’ reading. (see example table). I want to replace that zero-value with an interpolated value from the previous and next value by means of an update statement via a cte. When I try to update with a hardcoded float value, it works alright. However, when I try to update with the interpolated value, an update is performed but when I look at the table, the value is missing? What I'm I doing wrong?

Any help greatly appreciated.

drop table if exists mysolar;

create table mysolar (
    time    timestamptz not null,
    energy  double precision
);

select create_hypertable('mysolar',by_range('time'));

insert into mysolar(time,energy)
values
('2023-03-09 23:17:55',171.8090057),
('2023-03-09 23:18:55',171.8090057),
('2023-03-09 23:19:55',171.8090057),
('2023-03-09 23:20:55',171.8090057),
('2023-03-09 23:21:55',0),
('2023-03-09 23:22:55',171.8099976),
('2023-03-09 23:23:55',171.8099976),
('2023-03-09 23:24:55',171.8099976),
('2023-03-09 23:25:55',171.8099976);


with zerovalues as
(
    select 
        time,
        energy,
        lag(energy,1) over (order by time) prev_energy,
        lead(energy,1) over (order by time) next_energy
    from 
        mysolar
) 
update mysolar 
set 
    --energy = 13.52
    energy = zerovalues.prev_energy + ((zerovalues.next_energy - zerovalues.prev_energy)/2)
from
    zerovalues
where
    mysolar.energy=0
;
/*
select 
    time,
    energy,
    prev_energy,
    next_energy,
    prev_energy + ((next_energy - prev_energy)/2) ipol
from zerovalues;
*/

Answer 1

The problem is with your update statement and how the CTE is being used. When you join the CTE with the main table, you're not establishing a proper correlation between the rows in your CTE and the rows in your target table.

The key issue in your query is that you're not properly correlating the rows between your CTE (zerovalues) and the main table (mysolar) in your UPDATE statement.

In your original query, you have:

update mysolar 
set energy = zerovalues.prev_energy + ((zerovalues.next_energy - zerovalues.prev_energy)/2)
from zerovalues
where mysolar.energy = 0;

This doesn't establish a link between specific rows in zerovalues and mysolar. PostgreSQL doesn't automatically knows which row in zerovalues corresponds to which row in mysolar so the update might not be updating with the values you expect.

To fix that, add another where condition to match the rows between the two tables. Since time should be unique in this dataset, you can use it as the correlation key:

update mysolar 
set energy = zv.prev_energy + ((zv.next_energy - zv.prev_energy) / 2) 
from zerovalues zv 
where mysolar.time = zv.time 
and 
mysolar.energy = 0;

Answer 2

Your cte code is ok. Use it as a MERGE INTO and all will be ok. Comments in code...

drop table if exists mysolar;

--     S a m p l e    D a t a : 
create table mysolar ( time    timestamptz not null, energy  double precision );
insert into mysolar(time,energy)
values  ('2023-03-09 23:17:55',171.8090057),
        ('2023-03-09 23:18:55',171.8090057),
        ('2023-03-09 23:19:55',171.8090057),
        ('2023-03-09 23:20:55',171.8090057),
        ('2023-03-09 23:21:55',0),
        ('2023-03-09 23:22:55',171.8099976),
        ('2023-03-09 23:23:55',171.8099976),
        ('2023-03-09 23:24:55',171.8099976),
        ('2023-03-09 23:25:55',171.8099976);
Select * From mysolar Order By time;

time	energy
2023-03-09 23:17:55+00	171.8090057
2023-03-09 23:18:55+00	171.8090057
2023-03-09 23:19:55+00	171.8090057
2023-03-09 23:20:55+00	171.8090057
2023-03-09 23:21:55+00	0
2023-03-09 23:22:55+00	171.8099976
2023-03-09 23:23:55+00	171.8099976
2023-03-09 23:24:55+00	171.8099976
2023-03-09 23:25:55+00	171.8099976

Here is your CTE definition and selection placed into USING() clause of the MERGE INTO statement...
... added Where clause to filter just rows with energy = 0

Merge Into mysolar ms
USING ( WITH 
         zerovalues as
           ( Select   time, energy, 
                      Lag(energy,1) Over(order by time) as prev_energy,
                      Lead(energy,1) Over(order by time) as next_energy
             From     mysolar
           ) 
       Select    time, energy, prev_energy, next_energy,
                 prev_energy + ((next_energy - prev_energy)/2) as ipol
       From      Zerovalues
-- Add Where clause to filter just rows of interest (could be done in ON clause too but here it will reduce joins significantly)
       Where     energy = 0
   ) x ON( x.time = ms.time )  -- ON conditions joining your table with USING(query)
WHEN MATCHED THEN    -- when matched - do the update...
Update SET energy = x.ipol;

... checking the result ...

Select * From mysolar Order By time;

| time                   | energy             |
|------------------------|--------------------|
| 2023-03-09 23:17:55+00 | 171.8090057        |
| 2023-03-09 23:18:55+00 | 171.8090057        |
| 2023-03-09 23:19:55+00 | 171.8090057        |
| 2023-03-09 23:20:55+00 | 171.8090057        |
| 2023-03-09 23:21:55+00 | 171.80950165000002 | <-- Updated row (you might want to round it to 7 decimals as others)
| 2023-03-09 23:22:55+00 | 171.8099976        |
| 2023-03-09 23:23:55+00 | 171.8099976        |
| 2023-03-09 23:24:55+00 | 171.8099976        |
| 2023-03-09 23:25:55+00 | 171.8099976        |

fiddle