Hallo,

with the window function lag there is a shift of one or more rows. Every
row connects to the previous row := lag(column,1).

What I am looking for:
ts c c2
.. 1 1 -- or null in the first row
.. 2 3 -- it is the result of 1 + 2
.. 3 6 -- it is the result of 3 + 3
.. 4 10 -- it is the result of 6 + 4


Franz

On 8/9/20 12:38 PM, Samed YILDIRIM wrote:

 Hi Franz,
 Simply you can use window functions[1][2].
 pgsql-sql=# select *, lag(c) over (order by ts) as c2 from tt;
 ts | c | c2
 ---------------------+---+----
 2019-12-31 00:00:00 | 1 |
 2020-01-01 00:00:00 | 2 | 1
 2020-07-02 00:00:00 | 3 | 2
 2020-07-06 00:00:00 | 4 | 3
 2020-07-07 00:00:00 | 5 | 4
 2020-07-08 00:00:00 | 6 | 5
 (6 rows)
 I personally prefer to use window functions due to their simplicity. If
 you still want to use recursive query: [3]
 pgsql-sql=# with recursive rc as (
 select * from (select ts,c,null::numeric as c2 from tt order by ts asc
 limit 1) k1
 union
 select * from (select tt.ts,tt.c,rc.c as c2 from tt, lateral (select *
 from rc) rc where tt.ts > rc.ts order by tt.ts asc limit 1) k2
 )
 select * from rc;
 ts | c | c2
 ---------------------+---+----
 2019-12-31 00:00:00 | 1 |
 2020-01-01 00:00:00 | 2 | 1
 2020-07-02 00:00:00 | 3 | 2
 2020-07-06 00:00:00 | 4 | 3
 2020-07-07 00:00:00 | 5 | 4
 2020-07-08 00:00:00 | 6 | 5
 (6 rows)
 [1]: https://www.postgresql.org/docs/12/functions-window.html
 [2]: https://www.postgresql.org/docs/12/tutorial-window.html
 [3]: https://www.postgresql.org/docs/12/queries-with.html
 Best regards.
 Samed YILDIRIM
 09.08.2020, 09:29, "ml@ft-c.de" <ml@ft-c.de>:
 
     Hello,
 
     the table
     create table tt (
         ts timestamp,
         c numeric) ;
 
     insert into tt values
        ('2019-12-31',1), ('2020-01-01',2),
        ('2020-07-02',3), ('2020-07-06',4),
        ('2020-07-07',5), ('2020-07-08',6);
 
     My question: It is possible to get an
         additional column (named c2)
         with
         ( c from current row ) + ( c2 from the previous row ) as c2
 
     the result:
     ts c c2
     .. 1 1 -- or null in the first row
     .. 2 3
     .. 3 6
     .. 4 10
     ...
 
     with recursive ema as ()
     select ts, c,
         -- many many computed_rows
         -- <code> as c2
     from tt -- <- I need tt on this place
 
 
     thank you for help
     Franz