PostgreSQL 19 introduces several SQL enhancements that make complex queries easier to write and maintain. In this article, we will focus on three changes that address common problems in application development and analytical SQL:
IGNORE NULLSandRESPECT NULLSfor selected window functions.INSERT ... ON CONFLICT DO SELECT ... RETURNINGfor atomic insert-or-select operations.- Improvements to
GROUP BYprocessing when target-list subqueries reference grouping expressions.
PostgreSQL 18 vs PostgreSQL 19 at a glance
| Feature | PostgreSQL 18 | PostgreSQL 19 |
Window-function NULL handling | Always uses RESPECT NULLS for the five functions (discussed later) | Supports RESPECT NULLS and IGNORE NULLS |
INSERT ... ON CONFLICT | Supports DO NOTHING and DO UPDATE | Adds DO SELECT, including optional row locking |
| Returning an existing conflicting row | DO NOTHING RETURNING does not return the conflicting row | DO SELECT ... RETURNING can return it without updating it |
| Grouping expressions inside target-list subqueries | Some valid expression references fail grouping validation | Improves recognition of grouping-expression references |
| GROUPING() in target-list subqueries | Has a limitation in the affected alias-handling cases | Fixes the related bug |
1. IGNORE NULLS and RESPECT NULLS in window functions
The problem in PostgreSQL 18
Window functions calculate values across related rows without reducing the result to one row per group.
For example, LAG() retrieves a value from a preceding row, LEAD() retrieves a value from a following row, and LAST_VALUE() retrieves a value from the last row in the current window frame.
In PostgreSQL 18, these functions always respect NULL values. If the preceding row contains NULL, LAG() returns NULL even if an earlier non-NULL value exists.
The PostgreSQL 18 documentation explicitly states that the SQL-standard NULL-treatment options are not implemented.
What changes in PostgreSQL 19?
PostgreSQL 19 adds support for two options, written after the function call and before OVER:
RESPECT NULLS:retain the traditional behavior and treat NULL values as eligible values.IGNORE NULLS:skip NULL values when locating the value to return.
The options are supported by five functions only: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() and NTH_VALUE(). The default remains RESPECT NULLS, so existing queries retain their previous behavior.
Setup for example: Create a sensor-reading table
CREATE TABLE sensor_readings (
device_id integer,
ts timestamptz,
temperature numeric
);
INSERT INTO sensor_readings VALUES
(1, '2026-10-20 00:00', 20.5),
(1, '2026-10-20 01:00', NULL),
(1, '2026-10-20 02:00', NULL),
(1, '2026-10-20 03:00', 22.1),
(1, '2026-10-20 04:00', NULL),
(2, '2026-10-20 00:00', NULL),
(2, '2026-10-20 01:00', 18.0),
(2, '2026-10-20 02:00', NULL);Assume that a NULL temperature means that the sensor did not record a reading for that hour.
Example 1: Compare LAG() behavior
The following query works similar on PostgreSQL 18 and PostgreSQL 19:
postgres=# SELECT
device_id,
to_char(ts, 'HH24:MI') AS reading_time,
temperature,
LAG(temperature) OVER w AS previous_reading
FROM sensor_readings
WINDOW w AS (
PARTITION BY device_id
ORDER BY ts
)
ORDER BY device_id, ts;
device_id | reading_time | temperature | previous_reading
-----------+--------------+-------------+------------------
1 | 00:00 | 20.5 |
1 | 01:00 | | 20.5
1 | 02:00 | |
1 | 03:00 | 22.1 |
1 | 04:00 | | 22.1
2 | 00:00 | |
2 | 01:00 | 18.0 |
2 | 02:00 | | 18.0
(8 rows)At 03:00, the previous row contains NULL. Consequently, LAG() returns NULL.
Example 2: Skip NULL values using IGNORE NULLS
On PostgreSQL 19, change the query to:
SELECT
device_id,
to_char(ts, 'HH24:MI') AS reading_time,
temperature,
LAG(temperature) IGNORE NULLS OVER w
AS previous_non_null_reading
FROM sensor_readings
WINDOW w AS (
PARTITION BY device_id
ORDER BY ts
)
ORDER BY device_id, ts;
device_id | reading_time | temperature | previous_non_null_reading
-----------+--------------+-------------+---------------------------
1 | 00:00 | 20.5 |
1 | 01:00 | | 20.5
1 | 02:00 | | 20.5
1 | 03:00 | 22.1 | 20.5
1 | 04:00 | | 22.1
2 | 00:00 | |
2 | 01:00 | 18.0 |
2 | 02:00 | | 18.0
(8 rows)Now, at 03:00, LAG() returns 20.5 instead of NULL.
The function searches backward for the previous non-NULL value. For LAG() and LEAD(), the offset is counted across non-NULL values when IGNORE NULLS is specified.
This is useful when analyzing sparse sensor readings, transaction histories, financial data, and other time-series records.
Example 3: Forward-fill missing values with LAST_VALUE()
A common requirement is to carry forward the last known value until a new reading is available. In PostgreSQL 19, this can be expressed directly:
SELECT
device_id,
to_char(ts, 'HH24:MI') AS reading_time,
temperature,
LAST_VALUE(temperature) IGNORE NULLS OVER (
PARTITION BY device_id
ORDER BY ts
) AS filled_temperature
FROM sensor_readings
ORDER BY device_id, ts;
device_id | reading_time | temperature | filled_temperature
-----------+--------------+-------------+--------------------
1 | 00:00 | 20.5 | 20.5
1 | 01:00 | | 20.5
1 | 02:00 | | 20.5
1 | 03:00 | 22.1 | 22.1
1 | 04:00 | | 22.1
2 | 00:00 | |
2 | 01:00 | 18.0 | 18.0
2 | 02:00 | | 18.0
(8 rows)The default ordered window frame ends at the current row’s last peer. With the unique timestamps in this example, that makes the query a forward-fill operation. If the timestamps are not unique, add a deterministic tie-breaker to the ORDER BY clause.
Example 4: Find the first and second non-NULL readings
SELECT DISTINCT
device_id,
FIRST_VALUE(temperature) IGNORE NULLS OVER w
AS first_reading,
NTH_VALUE(temperature, 2) IGNORE NULLS OVER w
AS second_reading
FROM sensor_readings
WINDOW w AS (
PARTITION BY device_id
ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)
ORDER BY device_id;
device_id | first_reading | second_reading
-----------+---------------+----------------
1 | 20.5 | 22.1
2 | 18.0 |
(2 rows)The explicit frame allows each row to see the complete device partition. Device 2 has only one non-NULL reading, so its second reading is NULL.
How was this handled in PostgreSQL 18?
A common workaround uses a running count of non-NULL values to identify groups and then applies a window function to those groups.
WITH grouped_readings AS (
SELECT *,
COUNT(temperature) OVER (
PARTITION BY device_id
ORDER BY ts
) AS grp
FROM sensor_readings
)
SELECT
device_id,
ts,
temperature,
FIRST_VALUE(temperature) OVER (
PARTITION BY device_id, grp
ORDER BY ts
) AS filled_temperature
FROM grouped_readings
ORDER BY device_id, ts;
device_id | ts | temperature | filled_temperature
-----------+---------------------------+-------------+--------------------
1 | 2026-10-20 00:00:00+05:30 | 20.5 | 20.5
1 | 2026-10-20 01:00:00+05:30 | | 20.5
1 | 2026-10-20 02:00:00+05:30 | | 20.5
1 | 2026-10-20 03:00:00+05:30 | 22.1 | 22.1
1 | 2026-10-20 04:00:00+05:30 | | 22.1
2 | 2026-10-20 00:00:00+05:30 | |
2 | 2026-10-20 01:00:00+05:30 | 18.0 | 18.0
2 | 2026-10-20 02:00:00+05:30 | | 18.0
(8 rows)This technique works for forward-filling the example data, but it requires additional query logic. PostgreSQL 19 lets developers express the intent directly with LAST_VALUE() IGNORE NULLS.
Important considerations
- IGNORE NULLS does not remove rows from the final result.
- FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() still respect the window frame boundaries.
- The SQL-standard FROM FIRST and FROM LAST options for NTH_VALUE() are not implemented; reversing the ordering is the documented alternative for searching from the end.
- Skipping NULL values may change the meaning of a calculation if NULL represents a business state rather than missing data.
- With IGNORE NULLS, the executor may need to revisit rows to find non-NULL values, so test performance on large partitions with long runs of NULLs.
- The null-treatment keyword goes after the closing parenthesis of the function call (the SQL-standard placement). If you port queries from a dialect that accepts it inside the parentheses, move it.
2. INSERT … ON CONFLICT DO SELECT … RETURNING
The problem in PostgreSQL 18
PostgreSQL has supported INSERT … ON CONFLICT since PostgreSQL 9.5.
Before PostgreSQL 19, the available conflict actions were:
- DO NOTHING: skip inserting a row when a qualifying conflict occurs.
- DO UPDATE: update the existing conflicting row.
Consider an API that receives a request to create a user. If that user already exists, the application still needs the existing user’s ID.
Using DO NOTHING RETURNING does not return the conflicting row. Using DO UPDATE can return it, but it modifies the row even when the application only needs to retrieve its ID.
Example 1: Create a users table
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL
);
INSERT INTO users (email, name)
VALUES ('lokes@example.com', 'Lokes');
Now try inserting the same email again:
INSERT INTO users (email, name)
VALUES ('lokes@example.com', 'Lokes M')
ON CONFLICT (email) DO NOTHING
RETURNING id;
id
----
(0 rows)
INSERT 0 0Because the email already exists, the insert is skipped and the statement returns no row (0 rows). This is identical on PostgreSQL 18 and 19, because DO NOTHING did not change. It is the baseline: the new behavior appears only with DO SELECT in Example 3, which PostgreSQL 18 rejects with a syntax error.
Example 2: The PostgreSQL 18 workaround
A common workaround uses a no-op update:
INSERT INTO users (email, name)
VALUES ('lokes@example.com', 'Lokes M')
ON CONFLICT (email)
DO UPDATE SET name = users.name
RETURNING id;
id
----
1
(1 row)
INSERT 0 1This returns the existing ID, but it takes the UPDATE path.
An UPDATE creates a new row version even when the assigned value is unchanged. That can create dead tuples, generate additional WAL, and require further vacuum work. Index updates depend on the columns involved and whether a HOT update is possible.
For applications performing frequent duplicate requests, avoiding unnecessary updates can be valuable.
Example 3: PostgreSQL 19 adds DO SELECT
PostgreSQL 19 introduces a third action:
INSERT INTO users (email, name)
VALUES ('lokes@example.com', 'Lokes M')
ON CONFLICT (email) DO SELECT
RETURNING id, email, name;
id | email | name
----+------------------+------
1 | lokes@example.com | Lokes
(1 row)The behavior is:
- If the email does not exist, PostgreSQL inserts the new row and returns it.
- If the email already exists, PostgreSQL returns the existing row without updating it.
In this example, the existing name remains Lokes; the proposed name Lokes M is ignored on the conflict path.
This pattern is often called get-or-create or idempotent insert.
Example 4: Insert multiple rows and identify which were inserted
RETURNING can refer to OLD and NEW row values (this was introduced in PostgreSQL 18). What PostgreSQL 19 adds is that these also work with ON CONFLICT DO SELECT: for a freshly inserted row, all OLD values are NULL, while for a conflicting row, they hold the existing values. That makes it easy to distinguish inserts from conflicts.
INSERT INTO users (email, name)
VALUES
('lokes@example.com', 'Lokes M'),
('ravi@example.com', 'Ravi')
ON CONFLICT (email) DO SELECT
RETURNING
id,
email,
name,
(OLD.id IS NULL) AS was_inserted;Assuming Lokes already exists and Ravi does not, the result contains both rows:
id | email | name | was_inserted
----+-------------------+-------+--------------
1 | lokes@example.com | Lokes | f
6 | ravi@example.com | Ravi | t
The result order is not guaranteed. The existing row remains unchanged, while the new row is inserted. On the DO SELECT path, the NEW values equal the OLD values, since nothing is modified.
Identity or sequence values may be consumed by conflicting insert attempts, so gaps in generated IDs are normal.
Example 5: Select and lock the conflicting row
Some applications must read a row and protect it from concurrent updates while processing a request.
PostgreSQL 19 supports locking clauses with DO SELECT:
BEGIN;
INSERT INTO users (email, name)
VALUES ('Lokes@example.com', 'Lokes')
ON CONFLICT (email) DO SELECT FOR UPDATE
RETURNING id, email, name;
COMMIT;
BEGIN
id | email | name
----+-------------------+-------
7 | Lokes@example.com | Lokes
(1 row)
INSERT 0 1
COMMITIf the insert conflicts, PostgreSQL locks the existing row with FOR UPDATE. The lock remains until the transaction ends.
The available locking clauses are:
- FOR UPDATE
- FOR NO KEY UPDATE
- FOR SHARE
- FOR KEY SHARE
Use locking only when required by the application’s concurrency rules, since locks can make other transactions wait.
Example 6: Return a conflicting row only when a condition is met
INSERT INTO users AS u (email, name)
VALUES ('lokes@example.com', 'Lokes M')
ON CONFLICT (email) DO SELECT
WHERE u.name IS DISTINCT FROM EXCLUDED.name
RETURNING u.id, u.email, u.name;
id | email | name
----+-------------------+-------
1 | lokes@example.com | Lokes
(1 row)
INSERT 0 1
Here:
- u.name refers to the existing row.
- EXCLUDED.name refers to the proposed insert value.
- The condition determines whether the conflicting row is returned.
If the names are equal, the conflicting row is not returned. If they differ, the existing row is returned without changing it.
When a locking clause is specified, conflicting rows are still locked even if the condition prevents them from being returned.
Example 7:Return the conflicting row only when a condition is met
INSERT INTO users AS u (email, name)
VALUES ('lokes@example.com', 'Lokes M')
ON CONFLICT (email) DO SELECT
WHERE u.name IS DISTINCT FROM EXCLUDED.name
RETURNING u.id, u.email, u.name;
id | email | name
----+------------------+--------
10 | lokes@example.com | Lokes M
(1 row)PostgreSQL 18 vs. PostgreSQL 19
| Command | PostgreSQL 18 | PostgreSQL 19 |
| ON CONFLICT DO NOTHING | Supported | Supported (same behavior) |
| ON CONFLICT DO UPDATE | Supported | Supported (same behavior) |
| No-op DO UPDATE … RETURNING id workaround | Works | Works |
| ON CONFLICT DO SELECT … RETURNING | Not available (syntax error) | Supported |
| DO SELECT FOR UPDATE / SHARE / … | Not available | Supported |
| DO SELECT … WHERE | Not available | Supported |
| OLD/NEW in RETURNING with DO SELECT | Not available | Supported |
Important considerations
- A conflict target is required for DO SELECT, just as it is for DO UPDATE. Use an appropriate unique index or constraint.
- RETURNING is mandatory with DO SELECT.
- SELECT privileges are required. When using a locking clause, UPDATE privilege on at least one column is also required.
- Exclusion constraints cannot serve as arbiters for DO SELECT; only non-deferrable constraints and unique indexes are supported.
- DO SELECT does not replace DO UPDATE when the existing row must actually change.
- The new syntax is PostgreSQL-version-specific. Applications connecting to PostgreSQL 18 or earlier cannot use it.
Key takeaway: PostgreSQL 19 provides a direct, atomic way to insert a row or retrieve its existing conflicting counterpart without performing an unnecessary update.
3. GROUP BY improvements for target-list subqueries
Understanding the problem
GROUP BY combines rows with matching grouping expressions. PostgreSQL validates that references in the query are either grouped, aggregated, or otherwise valid under its grouping rules.
Consider a query that groups by an expression, such as UPPER(region_code), and contains a scalar subquery in the outer SELECT list that refers to the same expression.
PostgreSQL 18 matches only plain columns inside such a subquery, so it can fail to recognize that the reference corresponds to the grouping expression. In affected cases, it reports an error similar to:
ERROR: subquery uses ungrouped column from outer queryPostgreSQL 19 (commit 415100aa6) improves grouping-expression matching for target-list subqueries that reference non-subquery columns. It also fixes a related issue involving GROUPING() and target-list subquery aliases.
Setup for example: Create sample tables
CREATE TABLE sales (
region_code text,
qty integer,
order_ts timestamptz
);
CREATE TABLE region_labels (
code text PRIMARY KEY,
label text
);
INSERT INTO region_labels VALUES
('EU', 'Europe'),
('NA', 'North America');
INSERT INTO sales VALUES
('eu', 5, '2026-09-03'),
('EU', 7, '2026-09-20'),
('na', 2, '2026-10-01');Notice that the region codes are not consistently uppercase. We want to normalize them before grouping.
Example 1: Group by an expression and reuse it inside a subquery
SELECT
(
SELECT r.label
FROM region_labels AS r
WHERE r.code = UPPER(s.region_code)
) AS region_name,
SUM(s.qty) AS total_qty
FROM sales AS s
GROUP BY UPPER(s.region_code);
The intended result is:
region_name | total_qty
---------------+-----------
North America | 2
Europe | 12
(2 rows)
The outer query groups rows by UPPER(s.region_code). The scalar subquery uses the same expression to find the corresponding region label.
PostgreSQL 18: this query shape can fail grouping validation because the reference to s.region_code inside the subquery is not recognized as matching the outer grouping expression.
PostgreSQL 19: the improved matching supports this pattern, so the query returns the grouped totals and their labels.
Example 2: A workaround for PostgreSQL 18
One alternative is to perform the grouping in a derived table first, then do the lookup outside the aggregation:
SELECT
r.label AS region_name,
g.total_qty
FROM (
SELECT
UPPER(region_code) AS normalized_region,
SUM(qty) AS total_qty
FROM sales
GROUP BY UPPER(region_code)
) AS g
JOIN region_labels AS r
ON r.code = g.normalized_region;
region_name | total_qty
---------------+-----------
Europe | 12
North America | 2
(2 rows)This approach makes the grouping result explicit before the label lookup. It is a useful workaround when supporting older PostgreSQL versions, and it can remain a good query design when it improves readability.
Example 3: GROUPING() and grouping sets
PostgreSQL provides GROUPING() to identify which grouping expressions are absent from a grouping-set result. This is particularly useful with ROLLUP, CUBE, and GROUPING SETS.
For example:
SELECT
UPPER(region_code) AS region,
GROUPING(UPPER(region_code)) AS grouping_flag,
SUM(qty) AS total_qty
FROM sales
GROUP BY ROLLUP (UPPER(region_code));
region | grouping_flag | total_qty
--------+---------------+-----------
| 1 | 14
NA | 0 | 2
EU | 0 | 12
(3 rows)The grouping_flag distinguishes detail rows from the grand-total row:
- 0: the expression participates in the grouping level.
- 1: the expression is omitted at that grouping level.
This example shows how GROUPING() works in general and works on PostgreSQL 18 as well. The PostgreSQL 19 fix concerns GROUPING() when it appears inside target-list subqueries; the general purpose of GROUPING() is unchanged.
What about GROUP BY ALL?
GROUP BY ALL is a separate feature and is not part of PostgreSQL 19. It was committed in 2025 but reverted in July 2026 because it mishandled items that also appear in ORDER BY and could return wrong results. It is expected to be retried for PostgreSQL 20, so do not rely on it in PostgreSQL 19.
PostgreSQL 18 vs. PostgreSQL 19
| Behavior | PostgreSQL 18 | PostgreSQL 19 |
| Basic grouping by columns | Supported | Supported |
| Grouping by expressions | Supported | Supported |
| Target-list subquery reusing a grouped expression | Some affected cases fail validation | Improved support |
| GROUPING() in affected target-list subquery alias cases | Has a bug | Fixed |
| GROUP BY ALL | Not available | Not available (reverted) |
Key takeaway: PostgreSQL 19 improves the handling of valid grouped queries where a target-list subquery reuses an outer grouping expression. It is a query-correctness and expressiveness improvement, not a claim that every grouped query will execute faster.
Final comparison: Which workarounds can be simplified?
| PostgreSQL 18 pattern | PostgreSQL 19 alternative | Main benefit |
| Running-count or self-join workaround to skip NULL readings | LAG() IGNORE NULLS, LEAD() IGNORE NULLS, or LAST_VALUE() IGNORE NULLS | Clearer window-function logic |
| No-op DO UPDATE just to return an existing ID | ON CONFLICT DO SELECT … RETURNING | Avoids an unnecessary UPDATE |
| Derived-table workaround for affected grouped-expression subqueries | Reuse the grouping expression in the target-list subquery | Simpler query structure in supported cases |
These changes are especially relevant for developers maintaining analytical SQL, data-processing pipelines, API backends, and applications that rely on conflict-safe inserts.
Conclusion
PostgreSQL 19 introduces three useful SQL improvements.
IGNORE NULLS makes it easier to work with incomplete data in window functions. INSERT ... ON CONFLICT DO SELECT ... RETURNING provides a direct solution for atomic get-or-create operations without modifying an existing row unnecessarily. Improvements to GROUP BY processing allow more complex target-list subqueries to reuse grouping expressions correctly.
The main benefit is that developers can express their intent directly in SQL rather than maintaining workarounds.
Before adopting these features, test the examples on PostgreSQL 19 and compare them with PostgreSQL 18. For production systems, also validate transaction behavior, execution plans, locking, and compatibility with the versions used by your applications.