Before diving into this final post covering new Postgres 19 features, I just want to say it's been a wild ride. Postgres 19 has been a veritable treasure trove of enhancements, perhaps more than any previous release; or maybe that's just my perception. Usually I just skim through the release notes and nod along, sometimes jotting down things that look interesting for later study. Maybe calling out each element that caught my eye was the right thing to do, to really show how far Postgres has come since the last release, rather than simply accepting the status quo. It's easy to miss an otherwise innocuous one-liner in a changelog.

In any case, every major Postgres release ships with a marquee feature or two, and I've covered several of them in this series on Postgres 19. Some will garner dedicated conference talks or a flurry of frantic blogs from equally zealous authors. The big-ticket items always get their time in the spotlight. But I'm not here to talk about that this week. Like the last article on a handful of unassuming new function calls, this week will focus on some syntax tweaks that may never really trend anywhere—the lost and unsung.

Why dedicate a whole article to syntactic sugar? Why fuss over features that, taken one at a time, save maybe four lines of SQL apiece? Because four lines of SQL, multiplied across every project we'll ever touch, becomes a veritable mountain of saved aggravation. Postgres 19 happens to bring a whole potpourri of these, and a few of them scratch itches I didn't even know I had.

Let's wander through my notes and see what turned up.

Something Old, Something New

Prior to the newer MERGE syntax, the humble ON CONFLICT clause was the Postgres Way™ to combine INSERT and UPDATE into a single statement. It can DO NOTHING and shrug off the duplicate, or DO UPDATE and merge new values into the old row. That's still its primary role.

But there's a third thing we want constantly, and Postgres never had a clean way to express it: get-or-create. Insert this row if it's new, and either way, return the row that's there now. The RETURNING clause is supposed to do this, but users encounter a rude awakening when they actually try to leverage it. You see, RETURNING only works if something actually happened.

Let's create a demonstration table with a sample row for this:

CREATE TABLE account (
  id       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email    TEXT NOT NULL UNIQUE,
  display  TEXT NOT NULL,
  created  TIMESTAMPTZ NOT NULL DEFAULT now()
INSERT INTO account (email, display) VALUES ('[email protected]', 'Ada Lovelace');

Here's what happens if we try RETURNING on a DO NOTHING action:

INSERT INTO account (email, display)
VALUES ('[email protected]', 'duplicate')
ON CONFLICT (email) DO NOTHING
RETURNING id, email;

 id | email 
----+-------
(0 rows)

Technically correct, since DO NOTHING did not produce any results to return. It's overly pedantic and ultimately not what the user likely wants. Here's the "fix" for that:

INSERT INTO account (email, display)
VALUES ('[email protected]', 'duplicate')
ON CONFLICT (email) DO UPDATE
SET email = excluded.email
RETURNING id, email;

But what did we actually gain here? We've abused the RETURNING clause by performing a no-op update simply to ensure there was something to return. That causes a pointless write, bloats the table, and burns a tuple every single time. As a result, the syntax left us empty-handed the one time we most wanted the existing row's details.

Now with Postgres 19, that behavior is finally a thing of the past thanks to the new DO SELECT syntax. Instead of updating the conflicting row or ignoring it, DO SELECT returns the existing row that collided with our proposed insert.

Here's how that duplicate insert attempt turns out now:

INSERT INTO account (email, display)
VALUES ('[email protected]', 'Ada L.')
ON CONFLICT (email) DO SELECT
RETURNING id, email, display;

 id |      email       |   display    
----+------------------+--------------
  1 | [email protected] | Ada Lovelace

Look closely at that display value. We proposed Ada L., but DO SELECT handed back Ada Lovelace, the value already stored. This action returns the existing row, not the one we tried and failed to insert, exactly as expected. When there's no conflict at all, the insert proceeds normally and RETURNING reports the brand new row:

INSERT INTO account (email, display)
VALUES ('[email protected]', 'Grace Hopper')
ON CONFLICT (email) DO SELECT
RETURNING id, email, display;

 id |      email       |   display    
----+------------------+--------------
  5 | [email protected] | Grace Hopper

One statement, two potential outcomes, exactly one returned row. It's what everyone thought RETURNING was doing this whole time. The problem was never RETURNING, but DO NOTHING.

But did you notice Grace landed on id 5 rather than 2? If you have been following along with this example, you'll recall we've executed several statements until now. I covered this behavior in Logically Sequenced, but ON CONFLICT deserves special attention. In order to work properly, ON CONFLICT preemptively obtains a value from the sequence associated with the identity column. Since they're not transactional, sequences don't rewind. That gap is expected and harmless, but ON CONFLICT often surprises users when they end up burning through more values than expected due to high insert conflict counts.

You Shall Not Pass!

Returning the existing row is great, but a get-or-create rarely exists in isolation. It usually sits inside a transaction that's about to do something with that row, which means we have a classic race on our hands. Between the moment DO SELECT hands us the row and the moment we act on it, another session could waltz in and change it out from under us.

Can we reserve the row for ourselves? It turns out that DO SELECT accepts the same row-level locking clauses as a normal SELECT, so we can lock the returned row against concurrent modification right in the statement:

BEGIN;

INSERT INTO account (email, display)
VALUES ('[email protected]', 'ignored')
ON CONFLICT (email) DO SELECT FOR UPDATE
RETURNING id, email;

 id |      email      
----+-----------------
  1 | [email protected]

That FOR UPDATE locks Ada's row exactly as if we'd selected it the long way around, which closes the window where someone else could modify her account before our transaction commits. The whole family is supported: FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE all work too, so we can pick the weakest lock that keeps us safe.

There's also an optional WHERE clause to decide whether the conflicting row even comes back. The condition filters the returned row, so we only get it when it matches:

INSERT INTO account (email, display)
VALUES ('[email protected]', 'ignored')
ON CONFLICT (email) DO SELECT
WHERE account.display = 'Nobody'
RETURNING id, email;

 id | email 
----+-------
(0 rows)

Ada's display name isn't Nobody, so the row is filtered out and we get nothing back, even though the conflict absolutely happened.

There are two additional things to keep in mind when using DO SELECT. Because we're reading data back out, the statement requires SELECT privilege on the table, not merely INSERT. Secondly, a RETURNING clause is mandatory. Leave it off and Postgres expresses its ire:

INSERT INTO account (email, display)
VALUES ('[email protected]', 'ignored')
ON CONFLICT (email) DO SELECT;

ERROR:  ON CONFLICT DO SELECT requires a RETURNING clause

Just think of the RETURNING clause as the column list we'd normally specify for a SELECT statement.

Group Therapy

Things in Postgres 19 improve when we want to handle multiple rows at a time as well. One very long-standing complaint about GROUP BY is that it requires manually listing every single non-aggregate column. Boo! Hiss! Well, that all ends with the introduction of GROUP BY ALL. It gathers every select-list expression that isn't an aggregate and groups by all of them automatically.

Consider a small sale table:

CREATE TABLE sale (
  id        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  region    TEXT NOT NULL,
  product   TEXT NOT NULL,
  sold_at   DATE NOT NULL,
  amount    NUMERIC(10,2) NOT NULL
);
INSERT INTO sale (region, product, sold_at, amount) VALUES
 ('West', 'Widget', '2026-01-05', 100.00),
 ('West', 'Widget', '2026-01-19', 150.00),
 ('West', 'Gadget', '2026-02-02', 200.00),
 ('East', 'Widget', '2026-01-11', 120.00),
 ('East', 'Gadget', '2026-02-15',  90.00),
 ('East', 'Gadget', '2026-02-23',  60.00);

The old way makes us name region and product twice:

SELECT region, product, sum(amount) AS total
  FROM sale
 GROUP BY region, product
 ORDER BY region, product;

 region | product | total  
--------+---------+--------
 East   | Gadget  | 150.00
 East   | Widget  | 120.00
 West   | Gadget  | 200.00
 West   | Widget  | 250.00

And here's the same query, letting Postgres infer the grouping for us:

SELECT region, product, sum(amount) AS total
  FROM sale
 GROUP BY ALL
 ORDER BY region, product;

 region | product | total  
--------+---------+--------
 East   | Gadget  | 150.00
 East   | Widget  | 120.00
 West   | Gadget  | 200.00
 West   | Widget  | 250.00

Identical results without all of the column duplication shenanigans. And it doesn't end there; computed expressions also come along for the ride. Consider this example with date_trunc(): 

SELECT region, date_trunc('month', sold_at) AS month, count(*), sum(amount)
  FROM sale
 GROUP BY ALL
 ORDER BY region, month;

 region |         month          | count |  sum   
--------+------------------------+-------+--------
 East   | 2026-01-01 00:00:00+00 |     1 | 120.00
 East   | 2026-02-01 00:00:00+00 |     2 | 150.00
 West   | 2026-01-01 00:00:00+00 |     2 | 250.00
 West   | 2026-02-01 00:00:00+00 |     1 | 200.00

What about window functions, which can legitimately reference an aggregate? Postgres treats them the same way it treats aggregates and leaves them out of the inferred grouping. So we can rank our regions by total without rank() accidentally becoming a grouping key:

SELECT region,
       sum(amount) AS region_total,
       rank() OVER (ORDER BY sum(amount) DESC) AS rnk
  FROM sale
 GROUP BY ALL
 ORDER BY rnk;

 region | region_total | rnk 
--------+--------------+-----
 West   |       450.00 |   1
 East   |       270.00 |   2

If the select list contains no aggregate or window function whatsoever, GROUP BY ALL groups by everything, which makes it behave just like SELECT DISTINCT. Handy, occasionally surprising, and yet does what it says on the tin. And actually, given how flexible this is, I'm personally never going back.

f there's any weakness here, it's that it might work too well. Since it always re-groups the results no matter how many columns are present, existing reports might suddenly grow unexpected new columns instead of resulting in an error. That could cause unexpected behavior in downstream parsers or spreadsheets that don't operate with column headers and only consider position. The lack of query errors makes that kind of thing harder to find. Of course, that’s what good CI/CD is for!

It's still worth it in my opinion, but your mileage may vary.

COPY That

Now we've got a tidy aggregated result. Naturally, the very next thing someone requests is for that result as JSON, because it's always JSON.

No worries! That's what row_to_json(), json_agg, and the other JSON companion functions are for, after all. But that only really helps if we already have a program which can immediately process JSON output. What about exporting that to an actual file on disk?

Well, we can try to export a single-column CSV with no header:

COPY (
  SELECT row_to_json(ROW(region, product, amount))
    FROM sale ORDER BY id LIMIT 3
) TO '/tmp/sales.json' (FORMAT CSV, HEADER FALSE);

But that ends up looking like this:

"{""f1"":""West"",""f2"":""Widget"",""f3"":100.00}"
"{""f1"":""West"",""f2"":""Widget"",""f3"":150.00}"
"{""f1"":""West"",""f2"":""Gadget"",""f3"":200.00}"

That's not... quite what we're looking for. Sadly, the only real way to perform this magic is by using some kind of program logic. Either to pipe COPY through to reformat properly, or to otherwise JSONify the contents instead of Postgres. Who wants to maintain that?

Instead, Postgres 19 teaches COPY to speak JSON directly through a new format option. By default it emits newline-delimited JSON, one complete object per line, which is the format streaming tools and log pipelines tend to expect:

COPY (
  SELECT region, product, amount
    FROM sale ORDER BY id LIMIT 3
) TO STDOUT (FORMAT JSON);
{"region":"West","product":"Widget","amount":100.00}
{"region":"West","product":"Widget","amount":150.00}
{"region":"West","product":"Gadget","amount":200.00}

There's a shorthand too. Since JSON can only mean a format here, we can drop the FORMAT keyword entirely and write STDOUT JSON. Naming a column list narrows each object to just those keys, exactly as it does for CSV.

The real payoff shows up with nested data. There are ways to combine row_to_json(), json_build_object(), and the other JSON functions to build the nested structures, but that’s a lot of work. COPY ... JSON doesn't have to worry about any of that. Arrays become JSON arrays and jsonb columns nest as standard objects:

CREATE TABLE event (
  id       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT NOT NULL,
  tags     TEXT[] NOT NULL,
  payload  JSONB NOT NULL
);

INSERT INTO event (name, tags, payload) VALUES
 ('signup',   ARRAY['web','beta'], 
  '{"plan":"free","referrer":"twitter"}'),
 ('purchase', ARRAY['mobile'],    
  '{"plan":"pro","amount":49.99,"items":[1,2,3]}');

COPY event TO STDOUT (FORMAT JSON);
{"id":1,"name":"signup","tags":["web","beta"],"payload":{"plan": "free", "referrer": "twitter"}}
{"id":2,"name":"purchase","tags":["mobile"],"payload":{"plan": "pro", "items": [1, 2, 3], "amount": 49.99}}

The tags array and the payload object both land as real JSON, without the need for escapes or convoluted string mangling. Unfortunately this only works for JSON output; there is no COPY FROM equivalent yet:

COPY sale FROM '/tmp/sales.json' JSON;

ERROR:  COPY FORMAT JSON is not supported for COPY FROM

Additionally, don't try to use HEADER, DELIMITER, NULL, or any of the other CSV-only format controls, as JSON has no need for them. But what it does support is a new FORCE_ARRAY parameter.

Why? Newline-delimited JSON is perfect for streaming, where each line is independent and a consumer can process tuples as they arrive. But the output as a whole isn't technically valid JSON. A real JSON validator or parsing library will likely reject the output and leave us somewhat embarrassed.

This is what happens if we add FORCE_ARRAY to the COPY command:

COPY (
  SELECT region, product, amount
    FROM sale ORDER BY id LIMIT 3
) TO STDOUT (FORMAT JSON, FORCE_ARRAY);
[
 {"region":"West","product":"Widget","amount":100.00}
,{"region":"West","product":"Widget","amount":150.00}
,{"region":"West","product":"Gadget","amount":200.00}
]

The leading-comma style looks a little unusual, but it's perfectly valid, and now the whole blob parses as a single JSON array containing three objects. That makes it a natural fit for writing a complete file suitable for a downstream tool. In natural Postgres fashion, this also applies to the psql \copy equivalent:

\copy event TO '/tmp/events.json' (FORMAT JSON, FORCE_ARRAY)

The result is actually well-formed, and as a result, it should be possible to hand events.json to a JSON parser, nested payload objects and all. In fact, let's do that with the jq utility:

jq -c '.[] | { name, plan: .payload.plan }' /tmp/events.json 

{"name":"signup","plan":"free"}
{"name":"purchase","plan":"pro"}

That wouldn't have worked if the file wasn't actually valid JSON. And with that, we've gone from "export some rows" to "produce a valid JSON document" without writing a single line of application code.

Final Thoughts

None of these enhancements are likely to feature in the Postgres 19 release notes overview where the most prominent items appear. ON CONFLICT DO SELECT, GROUP BY ALL, and COPY ... TO JSON are the small mercies, the quality-of-life touches that help us eliminate a few lines of boilerplate in favor of productivity. They're how we actually get things done day by day.

Well... maybe. The jury is still out; there's a chance GROUP BY ALL shows up in the overview. That's genuinely a massive shift in how people may end up using Postgres on an everyday basis. Imagine never having to modify the GROUP BY section of a query when adding or removing columns from the SELECT list ever again. The amount of unexpected syntax errors just dropped by an order of magnitude from that alone.

Anyway, what are you waiting for? The Postgres beta is just sitting there waiting to demonstrate all the amazing new capabilities I've covered over the last few weeks. I know you have JSON. I know you have GROUP BY queries. I know plenty of users annoyed by how ON CONFLICT used to work with RETURNING. If you're not already scrambling to download Postgres 19 purely based on this article, I'm not sure how else to convince you.

And if Postgres 19 contained enough augmentations to fuel several weeks of exposition, I can only imagine what Postgres 20 will bring!