PostgreSQL 19 SQL Features: DO SELECT, GROUP BY ALL

PostgreSQL 19 is still in beta, but four of its additions change SQL you probably write every week: ON CONFLICT DO SELECT for get-or-create, GROUP BY ALL for reporting queries, FOR PORTION OF for time-ranged updates, and JSON output from COPY. Each one deletes a workaround. We covered the operational side of the beta, REPACK and parallel autovacuum, in our PostgreSQL 19 Beta 1 write-up. This is the application-developer half.

Syntax below follows the Beta 1 announcement and the feature summaries linked at the end. Run every example on a beta instance before you copy it, because beta details can still change.

ON CONFLICT DO SELECT: get-or-create without the hack

Here is the problem. You want a row for an email address. If it exists, give it back. If not, create it. Concurrent requests will race.

Until now the cleanest answer looked like this:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
RETURNING id, email, name;

That DO UPDATE SET email = EXCLUDED.email is a no-op that exists only so RETURNING produces a row. It still writes a new row version, which means dead tuples, extra WAL and a trigger fire on a table you did not mean to touch. The alternative, DO NOTHING followed by a SELECT, takes two statements and leaves a gap where a concurrent delete can slip in.

PostgreSQL 19 says what you mean:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO SELECT
RETURNING id, email, name;

One statement, one round trip, and a conflicting row is returned as it already exists. If you are building idempotent APIs, this is the natural primitive for “create this record once, return it every time.”

One caveat that will bite someone. DO SELECT returns the existing row but does not change it. If your real intent is “create or refresh a timestamp,” you still want DO UPDATE. Use DO SELECT only when the existing row should win.

GROUP BY ALL: stop editing two lists

Anyone who writes dashboards knows the chore. You add region to the SELECT and forget it in GROUP BY, and Postgres tells you so with an error.

-- PostgreSQL 18
SELECT region, product, date_trunc('month', ordered_at) AS month,
       sum(total) AS revenue, count(*) AS orders
FROM orders
GROUP BY region, product, date_trunc('month', ordered_at);

-- PostgreSQL 19
SELECT region, product, date_trunc('month', ordered_at) AS month,
       sum(total) AS revenue, count(*) AS orders
FROM orders
GROUP BY ALL;

GROUP BY ALL groups by every output column that is not an aggregate or a window function. It also stops you repeating the date_trunc expression, which is where copy-paste mistakes live.

It is not standard SQL. DuckDB and Snowflake users already have it, so analytics code will port more easily to Postgres. The risk is quiet: if you later add a high-cardinality column to the SELECT, the grouping silently changes and your row count explodes. In code review, treat a changed SELECT list under GROUP BY ALL as a changed query, because it is.

FOR PORTION OF: temporal updates that split rows for you

PostgreSQL 18 added temporal constraints. PostgreSQL 19 extends the story to UPDATE and DELETE with FOR PORTION OF, which the beta announcement describes as complementing that earlier work.

Take a price table where each row is valid for a date range. You want to change the price for only March through May:

UPDATE product_prices
  FOR PORTION OF valid_during
  FROM '2026-03-01' TO '2026-06-01'
SET price = 19.00
WHERE product_id = 42;

If the existing row covers the whole of 2026, Postgres splits it into three: January to March at the old price, March to June at the new one, and June onward at the old price again. Before this, that split was a hand-written transaction with an UPDATE and two INSERTs, and it was exactly the kind of code that gets an off-by-one on the boundary dates.

If you build billing, subscription plans, insurance policies or anything with “valid from, valid to” columns, this is the feature to read the docs for first. Check the exact range semantics on your column type before trusting it with money.

COPY TO JSON: fewer shell pipelines

Exporting query results as JSON used to mean json_agg inside a query, or piping CSV through jq or a script. The beta adds native JSON output to COPY TO, producing newline-delimited JSON, one object per line:

COPY (SELECT id, email, created_at FROM users LIMIT 3)
TO STDOUT WITH (FORMAT json);

Newline-delimited output streams, so a multi-gigabyte export no longer has to build one giant array in memory. It also loads straight into tools that read NDJSON. Confirm the exact option spelling in the 19 docs, since this is the newest of the four features.

The rest of the beta worth a glance

The same release changes defaults that can affect you without any code change. JIT compilation is now off by default, default_toast_compression now defaults to lz4, and data checksums can be enabled or disabled without a restart. The beta also adds pg_plan_advice, an extension for stabilizing planner decisions. If you are tuning slow queries, our query optimization guide for application developers is the place to establish a baseline first.

What to do before general availability

1. Spin up the beta from a copy of production and run your test suite. Watch for the JIT and TOAST defaults changing timings.
2. Grep your codebase for DO UPDATE SET x = EXCLUDED.x no-ops. Those are your DO SELECT candidates once you are on 19.
3. Do not adopt GROUP BY ALL in code that must also run on 18 or on a managed service that is behind. Version support will lag for a while.
4. Wait for the final release and the first minor update before moving production. For where your current version stands, see our PostgreSQL 18 upgrade guide.

The takeaway: PostgreSQL 19 will remove four workarounds from everyday SQL. None of them is worth running a beta in production for, and all of them are worth testing now so your first week on 19 is boring.