Add a column based on the value of an existing column

Example data:

ID	VALUE
1	1
2	null
3	4
4	null
5	null
6	5
7	null
8	null
9	null
10	2

I hope to add a column VALUE_NOW.

The column is filled according to the VALUE. If VALUE has a record, the added column is equal to the value of VALUE. If the VALUE is NULL, then the added column is the value of the previous VALUE that is not NULL. (If you don't know the database I use, you can use PostgreSQL instead, thanks!)

Expected output:

ID	VALUE	VALUE_NOW
1	1	1
2		1
3	4	4
4		4
5		4
6	5	5
7		5
8		5
9		5
10	2	2

I tried:

SELECT
  ID,
  VALUE,
  COALESCE(VALUE, LAG(VALUE) OVER (ORDER BY ID)) VALUE_NOW
FROM my_table;

But the result is a little different from what I want:

ID	VALUE	VALUE_NOW
1	1	1
2		1
3	4	4
4		4
5
6	5	5
7		5
8
9
10	2	2

Solution

As you mentioned we can use a Postgres DB, you can use a trick of FIRST_VALUE with a windowed COUNT as "row number":

SELECT
  id, 
  value,
  FIRST_VALUE(sub.value) OVER (PARTITION BY sub.rn ORDER BY sub.id) AS value_now
FROM 
(
  SELECT
    id,
    value,
    COUNT(value) OVER (ORDER BY id) AS rn
  FROM my_table
  ORDER BY id
) AS sub;

Let me explain the idea:

Since Postgres doesn't provide the IGNORE NULL clause, we need to define a "row number" to determine the last not null value before the current row.

The function COUNT does ignore NULL values as intended. So we use this function to build a running number sorted by your ID column.

Then, whenever the value in a row is NULL, we take the previous NOT NULL value according to the number we determined.

See this sample fiddle with your data. It's easier to show than to explain ;)