Often in PostgreSQL you may want to fill null values in a table with the most recent previous value in the same column.
You can use the following syntax to do so:
SELECT sales_date,
amount,
first_value(amount) OVER (partition BY x) AS filled_amount
FROM (
SELECT *,
SUM(CASE WHEN amount is NOT NULL THEN 1 END) OVER (ORDER BY sales_date) AS x
FROM sales
) t
This particular example will replace the null values in the amount column of the table named sales with the most recent previous value in the column.
The following example shows how to use this syntax in practice.
Example: How to Fill Null Values with Previous Value in PostgreSQL
Suppose that we create the following table named sales that contains information about total sales made by some company during eight consecutive days.
-- create table named 'emp'
CREATE TABLE sales (
sales_date DATE,
amount INT
);
-- add rows to the table
INSERT INTO sales VALUES ('2024-01-01', 22);
INSERT INTO sales VALUES ('2024-01-02', 13);
INSERT INTO sales VALUES ('2024-01-03', NULL);
INSERT INTO sales VALUES ('2024-01-04', 10);
INSERT INTO sales VALUES ('2024-01-05', 25);
INSERT INTO sales VALUES ('2024-01-06', 30);
INSERT INTO sales VALUES ('2024-01-07', NULL);
INSERT INTO sales VALUES ('2024-01-08', 18);
-- view all rows in table
SELECT * FROM sales;
sales_date | amount
------------+--------
2024-01-01 | 22
2024-01-02 | 13
2024-01-03 |
2024-01-04 | 10
2024-01-05 | 25
2024-01-06 | 30
2024-01-07 |
2024-01-08 | 18
Notice that the amount column has several null values.
We can use the following syntax to replace each of the null values in the amount column with the most recent previous value in the column:
SELECT sales_date,
amount,
first_value(amount) OVER (partition BY x) AS filled_amount
FROM (
SELECT *,
SUM(CASE WHEN amount is NOT NULL THEN 1 END) OVER (ORDER BY sales_date) AS x
FROM sales
) t
sales_date | amount | filled_amount
------------+--------+---------------
2024-01-01 | 22 | 22
2024-01-02 | 13 | 13
2024-01-03 | | 13
2024-01-04 | 10 | 10
2024-01-05 | 25 | 25
2024-01-06 | 30 | 30
2024-01-07 | | 30
2024-01-08 | 18 | 18
Notice that the new filled_amount column has filled in each of the missing values in the amount column with the most recent previous value in the column.
Feel free to use similar syntax to replace null values with previous value in a column of your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Replace Null Values with Zero
PostgreSQL: How to Select Rows That Do Not Exist in Another Table
PostgreSQL: How to Find Minimum Value Across Multiple Columns
