SQL Forward Fill Generator (Last Non-NULL Value)
Time series and event tables often have gaps: a sensor reports a temperature only when it changes, a status column is set on some rows and NULL on the rest. Filling each NULL with the last known value (forward fill, also called LOCF) takes one window function in PostgreSQL 19, which adds IGNORE NULLS to last_value, first_value, lag and lead. Older PostgreSQL versions and SQLite need a workaround: a running count() of non-NULL values splits the rows into groups, and each group's single non-NULL value fills the rest. This generator writes either version for one or more columns, with optional PARTITION BY, backward fill for leading NULLs, linear interpolation between known points, a fallback constant, and an UPDATE statement if you want to store the result. Every query shape was compared row by row against a reference implementation on PostgreSQL 19 RC2, PostgreSQL 18.7 and SQLite 3.53. Everything runs in your browser.
- IGNORE NULLS in window functions needs PostgreSQL 19 or later. Use the PostgreSQL 18 and older option for earlier versions.
- The ORDER BY should be unique within each partition (add an id as a tiebreaker), otherwise rows with equal keys can take each other's values in any order.
Background reading: IGNORE NULLS in PostgreSQL 19 Window Functions and Fill Gaps in Time Series Data in PostgreSQL.
Do more than sql forward fill generator (last non-null value) — meet Chat2DB
Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.
How to use
- Choose your database, then enter the table, the PARTITION BY columns (for example sensor_id) and the ORDER BY column that defines time order.
- Add each column to fill and pick forward fill, backward fill, forward then backward, or linear interpolation, with an optional fallback value.
- Copy the SELECT to preview the result, or switch to UPDATE to write the filled values back, and run it in psql, sqlite3 or Chat2DB.
Frequently asked questions
How do I fill NULL values with the previous non-NULL value in PostgreSQL?
In PostgreSQL 19 and later, use last_value(col) IGNORE NULLS OVER (PARTITION BY id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). IGNORE NULLS skips NULL rows when the function looks back, so each row gets the most recent non-NULL value, or its own value if it has one. Write the ROWS frame explicitly: the default RANGE frame includes rows with the same ORDER BY value, which can pick up a later peer's value. In PostgreSQL 18 and older, IGNORE NULLS is a syntax error, so the generator uses count(col) OVER (... ROWS UNBOUNDED PRECEDING) to number the groups and max(col) OVER (PARTITION BY id, grp) to spread each group's value.
Why does the count() trick work for forward fill?
count(col) counts only non-NULL values. Run it as a window in time order and it increases by one on every row that has a value and stays the same on NULL rows. So each non-NULL row starts a new group number, and the NULL rows after it share that number. Every group then contains exactly one non-NULL value, the first row, and max(col) over that group returns it regardless of row order. Rows before the first value get group 0 and stay NULL, which is where backward fill or a fallback constant comes in. On a 1,000,000-row test in PostgreSQL 19 RC2, IGNORE NULLS took about 460 ms and the count() version about 620 ms, with identical results.
Can I write the filled values back into the table?
Yes. Switch the output to UPDATE. The generator wraps the window query in a subquery and joins it back by a row identifier: the primary key if you give one, otherwise ctid in PostgreSQL or rowid in SQLite (UPDATE ... FROM needs SQLite 3.33+). Window functions cannot appear directly in an UPDATE SET clause, which is why the subquery is needed. Run it inside a transaction and check the row count before committing. To check the output first, you can run the SELECT version in Chat2DB (https://chat2db.ai/download or https://app.chat2db.ai) against a copy of the table.
