We’re wrapping up our review of the March CommitFest for PostgreSQL 19. This fourth and final article in the series covers the changes related to SQL and to our DEV1 and DEV2 courses.
As a reminder, here’s the plan for our coverage of the final PostgreSQL 19 CommitFest:
Part 5.4 (SQL, DEV1, DEV2) – you are here 🙂
For the earlier CommitFests, see: 2025-07, 2025-09, 2025-11, 2026-01.
Here’s what’s covered:
postgres_fdw: remote transactions inherit READ ONLY and DEFERRABLE modes
Integrity constraints: altering the enforceability of CHECK constraints
Integrity constraints: changing the generation expression of virtual columns with CHECK constraints
Casting between uuid and bytea, and base32hex format support
INSERT … ON CONFLICT DO SELECT
commit: 88327092ff0
The INSERT … ON CONFLICT DO NOTHING RETURNING command handles a conflict correctly but doesn’t return the existing row. In the following example, the command adds a new row and returns it:
CREATE TABLE test (
id int PRIMARY KEY,
value numeric(4,2) DEFAULT random()
);
INSERT INTO test VALUES (1)
ON CONFLICT (id) DO NOTHING
RETURNING id, value;
id | value
----+-------
1 | 0.74
(1 row)
But if the row already exists, the RETURNING clause returns nothing:
INSERT INTO test VALUES (1)
ON CONFLICT (id) DO NOTHING
RETURNING id, value;
id | value
----+-------
(0 rows)
If an application needed INSERT to always return the row with the specified ID, it had to check the command’s completion status to see whether a row was inserted and, on conflict, run a separate SELECT. That’s an extra query, and another transaction could delete or modify the row between the two commands.
The new ON CONFLICT DO SELECT action returns either the new row or the existing conflicting row through RETURNING:
INSERT INTO test VALUES (1)
ON CONFLICT (id) DO SELECT
RETURNING id, value;
id | value
----+-------
1 | 0.74
(1 row)
You still list the columns to return in the RETURNING clause, and after SELECT you can specify a locking clause (FOR UPDATE, FOR SHARE, and so on) and a WHERE condition. A conflicting row that doesn’t satisfy the WHERE condition isn’t returned, but if a locking clause is specified, it’s still locked.
COPY FROM: replacing invalid values with NULL
commit: 2a525cc97e1
In version 17, COPY gained the ability to ignore rows with data type conversion errors. With the ON_ERROR ignore option, rows with errors are skipped during the load. Now you can also replace the invalid values with NULL and still load the row. NOT NULL constraints (including those on domains) are enforced, though: if a NULL ends up in such a column, the command fails with an error.
CREATE TABLE test (col int);
COPY test FROM STDIN WITH (on_error set_null);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> two
>> 3
>> \.
NOTICE: in 1 row, columns were set to null due to data type incompatibility
COPY 3
SELECT * FROM test;
col
-----
1
3
(3 rows)
COPY TO: exporting data in JSON format
commit: 7dadd38cda9, 4c0390ac53b
The COPY command can now export data in json, in addition to the text, csv, and binary formats:
COPY (SELECT * FROM bookings LIMIT 3) TO STDOUT WITH (format json);
{"book_ref":"2EW1SQ","book_date":"2025-09-01T03:00:12.557744+03:00","total_amount":8125.00}
{"book_ref":"3ZY3O5","book_date":"2025-09-03T03:25:35.278453+03:00","total_amount":7500.00}
{"book_ref":"756UAS","book_date":"2025-09-10T19:24:02.978559+03:00","total_amount":6325.00}
Add the FORCE_ARRAY option to wrap the output in an array:
COPY (SELECT * FROM bookings LIMIT 3) TO STDOUT WITH (format json, force_array);
[
{"book_ref":"2EW1SQ","book_date":"2025-09-01T03:00:12.557744+03:00","total_amount":8125.00}
,{"book_ref":"3ZY3O5","book_date":"2025-09-03T03:25:35.278453+03:00","total_amount":7500.00}
,{"book_ref":"756UAS","book_date":"2025-09-10T19:24:02.978559+03:00","total_amount":6325.00}
]
This works only for exporting data (COPY TO), not for loading it (COPY FROM). Also, the json format doesn’t support some of the command’s options: HEADER, DEFAULT, NULL, DELIMITER, FORCE_QUOTE, FORCE_NOT_NULL, and FORCE_NULL.
jsonpath: new string methods
commit: bd4f879a9cd
The jsonpath language gets new string methods: lower(), upper(), initcap(), replace(), split_part(), as well as btrim(), ltrim(), and rtrim():
SELECT jsonb_path_query('" hello, world! "', '$.btrim()'),
jsonb_path_query('"hello, world!"', '$.upper()'),
jsonb_path_query('"HELLO, WORLD!"', '$.lower()'),
jsonb_path_query('"hello, world!"', '$.initcap()'),
jsonb_path_query('"hello, world?"', '$.replace("?","!")'),
jsonb_path_query('"hello, world!"', '$.split_part(", ",1)')
\gx
-[ RECORD 1 ]----+----------------
jsonb_path_query | "hello, world!"
jsonb_path_query | "HELLO, WORLD!"
jsonb_path_query | "hello, world!"
jsonb_path_query | "Hello, World!"
jsonb_path_query | "hello, world!"
jsonb_path_query | "hello"
IS JSON predicate with domain types
commit: 3b4c2b9db25
The IS JSON predicate used to reject domains over json, jsonb, bytea, or text: the server didn’t resolve the domain’s base type.
CREATE DOMAIN js AS jsonb;
18=# SELECT '"Hello, World!"'::js IS JSON;
ERROR: cannot use type js in IS JSON predicate
This has been fixed:
19=# SELECT '"Hello, World!"'::js IS JSON;
?column?
----------
t
(1 row)
postgres_fdw: remote transactions inherit READ ONLY and DEFERRABLE modes
commit: de28140ded8
Previously, the remote transaction that postgres_fdw opens on a foreign server didn’t inherit the READ ONLY and DEFERRABLE modes of the local transaction. As a result, a local transaction declared as READ ONLY could modify data on the foreign server, for example, by calling functions. And a transaction declared as DEFERRABLE could abort due to a serialization failure on the foreign server.
Now these modes are passed on to the foreign server when the remote transaction is opened. This change is incompatible with previous versions: if your application modifies data through postgres_fdw in READ ONLY transactions, you’ll need to declare them as READ WRITE.
Integrity constraints: altering the enforceability of CHECK constraints
commit: 342051d73b3
Version 18 made it possible to create CHECK constraints as NOT ENFORCED:
CREATE TABLE test (id int);
ALTER TABLE test ADD CONSTRAINT check_id CHECK (id > 0) NOT ENFORCED;
But you couldn’t change such a constraint to ENFORCED.
18=# ALTER TABLE test ALTER CONSTRAINT check_id ENFORCED;
ERROR: cannot alter enforceability of constraint "check_id" of relation "test"
You had to drop and recreate it. Version 19 lifts this restriction. When you set a constraint to ENFORCED, the table is scanned to verify that the existing rows satisfy it:
19=# ALTER TABLE test ALTER CONSTRAINT check_id ENFORCED;
ALTER TABLE
Integrity constraints: changing the generation expression of virtual columns with CHECK constraints
commit: f80bedd52b1
Version 18 added support for virtual generated columns. For example:
CREATE TABLE test (
id int,
amount numeric,
tax numeric GENERATED ALWAYS AS (amount*0.2) VIRTUAL,
CONSTRAINT check_tax CHECK (tax > 0)
);
However, you couldn’t change the generation expression of such a column if it had a CHECK constraint:
18=# ALTER TABLE test ALTER COLUMN tax SET EXPRESSION AS (amount*0.22);
ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints
DETAIL: Column "tax" of relation "test" is a virtual generated column.
In version 19, this restriction is gone. The table isn’t rewritten, only scanned to re-verify the CHECK constraints:
19=# ALTER TABLE test ALTER COLUMN tax SET EXPRESSION AS (amount*0.22);
ALTER TABLE
Casting between uuid and bytea, and base32hex format support
commit: 497c1170cb1, ba21f5bf8af
Converting between uuid and bytea used to require a detour through the text representation, and the encode/decode functions didn’t support the base32hex format. Now there’s an explicit cast. The base32hex format is handy for UUIDv7: the resulting string is shorter than its hex representation and preserves the sort order (with the C collation). When converted to base32hex, UUID values are padded on the right with “=” characters. For a more compact representation, you can trim them with the rtrim function, since decode accepts base32hex input both with and without padding:
WITH cte(val) AS (
VALUES (uuidv7())
)
SELECT
val "text",
val::bytea "bytea",
encode(val::bytea, 'base32hex') "base32hex",
rtrim(encode(val::bytea, 'base32hex'),'=') "base32hex trimmed",
decode(rtrim(encode(val::bytea, 'base32hex'),'='), 'base32hex') "bytea decoded"
FROM cte
\gx
-[ RECORD 1 ]-----+-------------------------------------
text | 01a013f7-edf0-760d-a654-f2ec6b3a9709
bytea | \x01a013f7edf0760da654f2ec6b3a9709
base32hex | 06G17TVDU1R0R9IKUBM6MEKN14======
base32hex trimmed | 06G17TVDU1R0R9IKUBM6MEKN14
bytea decoded | \x01a013f7edf0760da654f2ec6b3a9709
PL/pgSQL: SELECT … INTO optimization
commit: ce8d5fe0e28
In PL/pgSQL, both of these constructs do the same thing: evaluate an expression and assign the value to a variable:
DECLARE
x int;
BEGIN
x := 2+2;
SELECT 2+2 INTO x;
END;
But they do it differently. In the first case, PL/pgSQL evaluates the expression directly, while in the second, a full query is executed through SPI, which is much slower. Now simple expressions (the ones that don’t access tables) are evaluated directly, without the SPI overhead, as long as the result is assigned to a single variable. A side effect: such expressions no longer show up in pg_stat_statements.
tid_block and tid_offset functions
commit: df6949ccf7a
Previously, to extract the block number and the offset within the block from a ctid value, you had to cast it to text and then to point:
SELECT ctid,
(ctid::text::point)[0]::bigint AS block,
(ctid::text::point)[1]::int AS offset
FROM bookings LIMIT 1;
ctid | block | offset
-------+-------+--------
(0,1) | 0 | 1
(1 row)
Not exactly convenient. The new functions do it directly:
SELECT ctid,
tid_block(ctid),
tid_offset(ctid)
FROM bookings LIMIT 1;
ctid | tid_block | tid_offset
-------+-----------+------------
(0,1) | 0 | 1
(1 row)
TOAST: lz4 as the default compression method
commit: 7c1849311e4, 34dfca29343
Historically, TOAST has used pglz, the compression method built into the server. However, version 14 added support for lz4, which is more efficient in terms of both compression ratio and CPU usage.
In version 19, if the server is built with lz4 support, lz4 becomes the default TOAST compression method:
SELECT setting ~ '--with-lz4' FROM pg_config() WHERE name = 'CONFIGURE';
?column?
----------
t
(1 row)
\dconfig default_toast_compression
List of configuration parameters
Parameter | Value
---------------------------+-------
default_toast_compression | lz4
(1 row)
That wraps up our review of the changes in version 19. All that’s left is to wait for the official PostgreSQL 19 release, scheduled for November 19, and to check the final list of changes in the Release Notes.