PostgreSQL 19 brings a set of smaller SQL features that make everyday queries shorter and safer. This post walks through thirteen features and compares each one with PostgreSQL 18, with examples.
At a glance
| # | Feature | PG 18 | PG 19 | Changes existing behavior? |
| 1 | JSON_ARRAY(SELECT …), zero rows | NULL | [] | Yes |
| 2 | jsonpath string methods | Not available | .lower(), .replace(), .split_part() … | No |
| 3 | IS JSON on domain types | Error, cast needed | Works directly | No |
| 4 | oid8 type | Not available | 64-bit OID type | No |
| 5 | uuid ↔ bytea casts | Go through hex text | u::bytea, b::uuid | No |
| 6 | base64url, base32hex | Not available | New encode/decode formats | No |
| 7 | random(min, max) for dates/timestamps | Numbers only | date, timestamp, timestamptz | No |
| 8 | range_minus_multi() | Error on split ranges | Returns rows | No |
| 9 | error_on_null() | Write your own function | Built in | No |
| 10 | Polish and Esperanto stemmers | Not available | Available; dutch uses a new stemmer | Yes (Dutch) |
| 11 | Temporal tables: FOR PORTION OF | Split and copy rows by hand | UPDATE / DELETE … FOR PORTION OF | No |
| 12 | tid_block() / tid_offset() | ctid::text::point trick | Built-in functions | No |
| 13 | regdatabase type | Join pg_database | oid::regdatabase cast | No |
1. JSON_ARRAY(SELECT …) returns [] instead of NULL
JSON_ARRAY() is the SQL-standard way to build a JSON array, either from a list of values or from a one-column subquery. In PG 19, the subquery form returns an empty array when the subquery finds no rows. PG 16–18 returned NULL in that case, because the subquery form was evaluated like an aggregate.
SELECT o.id,
JSON_ARRAY(SELECT i.sku FROM order_items i WHERE i.order_id = o.id) AS skus
FROM orders o;
-- order with no items: PG 18 → NULL PG 19 → []| Expression | PG 18 | PG 19 |
| JSON_ARRAY(SELECT …), zero rows | NULL | [] |
| JSON_ARRAY(a, b, c), JSON_ARRAY() | Unchanged | Unchanged |
| JSON_ARRAYAGG / json_agg over zero rows | NULL | NULL |
By default, JSON_ARRAY drops NULL values (ABSENT ON NULL). Write NULL ON NULL to keep them, for example JSON_ARRAY(1, NULL NULL ON NULL) → [1, null].
2. jsonpath string methods
-- PG 18: extract the value, then clean it with SQL functions
SELECT initcap(btrim(doc->>'name')) FROM customers; -- works in PG 18 and PG 19
SELECT jsonb_path_query(doc, '$.name.btrim().initcap()') FROM customers;
-- PG 18: ERROR: syntax error … of jsonpath input
-- PG 19: "Asha Rao"
The biggest benefit shows up in JSON_TABLE and filters:
-- PG 19
SELECT doc FROM customers
WHERE doc @? '$ ? (@.email.lower() == "asha@example.com")';
CREATE TABLE customers (doc jsonb);
INSERT INTO customers VALUES
('{"name":" asha RAO ","email":"Asha@Example.com","phone":"98-480-12345","tags":"vip,retail,south"}'),
('{"name":"ravi kumar","email":"ravi@example.com","phone":"99-000-11111","tags":"retail,north"}');
SELECT jt.*
FROM customers,
JSON_TABLE(doc, '$' COLUMNS (
name text PATH '$.name.btrim().initcap()',
phone text PATH '$.phone.replace("-", "")',
region text PATH '$.tags.split_part(",", -1)')) jt;
name | phone | region
------------+------------+--------
Asha Rao | 9848012345 | south
Ravi Kumar | 9900011111 | north
(2 rows)3. IS JSON on domain types
CREATE DOMAIN json_text AS text;
CREATE TABLE events (id int, raw json_text);
-- PG 18
SELECT id, raw IS JSON FROM events; -- ERROR
-- PG 19
ALTER TABLE events ADD CHECK (raw IS JSON); -- works without a castDomains over unsupported types, such as int, still fail. In PG 19 the error message shows the domain name you wrote rather than its base type. A CHECK constraint inside the domain itself (CHECK (VALUE IS JSON)) already worked in PG 18.
4. oid8: a 64-bit identifier type
oid8 is an unsigned 8-byte counterpart to the 4-byte oid. It supports comparison, btree and hash indexes, min/max, and casts to and from integers.
-- PG 18
SELECT 5000000000::oid8; -- ERROR: type "oid8" does not exist
SELECT 4294967296::bigint::oid; -- ERROR: OID out of range
-- PG 19
SELECT 5000000000::oid8; -- 5000000000
SELECT 42::oid::oid8; -- 42 (oid → oid8 is allowed)
SELECT 42::oid8::oid; -- ERROR (oid8 → oid is blocked)
5. Casts between uuid and bytea
PG 19 adds explicit casts in both directions. The cast copies the UUID’s raw 16 bytes, with no conversion through text.
-- PG 18
-- uuid → bytea
SELECT '1ea3d64c-bc40-4cc3-84bb-6b11ee31e5c2'::uuid::bytea;
-- ERROR: cannot cast type uuid to bytea
-- PG 18 workarounds (convert through hex text)
SELECT decode(replace(u::text, '-', ''), 'hex');
-- bytea → uuid
SELECT encode('\x1ea3d64cbc404cc384bb6b11ee31e5c2'::bytea, 'hex')::uuid;
-- PG 19
-- uuid → bytea
SELECT '1ea3d64c-bc40-4cc3-84bb-6b11ee31e5c2'::uuid::bytea;
-- \x1ea3d64cbc404cc384bb6b11ee31e5c2
-- bytea → uuid
SELECT '\x1ea3d64cbc404cc384bb6b11ee31e5c2'::bytea::uuid;
-- 1ea3d64c-bc40-4cc3-84bb-6b11ee31e5c2
-- short form, 24 characters
SELECT encode(gen_random_uuid()::bytea, 'base64'); 6. base64url and base32hex encodings
encode() and decode() gain two formats. base64url (RFC 4648 §5) uses – and _ instead of + and /, and has no = padding. base32hex (RFC 4648 §7) uses 0-9 A-V, so the encoded text sorts in the same order as the underlying bytes. Its decoder ignores case and whitespace.
-- PG 18
SELECT encode('\xfbff'::bytea, 'base64url');
-- ERROR: unrecognized encoding: "base64url"
-- PG 18 workaround
SELECT translate(rtrim(replace(encode('\xfbff'::bytea, 'base64'), E'\n', ''), '='), '+/', '-_');
-- OUTPUT: -_8
-- PG 19
SELECT encode('\xfbff'::bytea, 'base64url'); -- OUTPUT: -_8
SELECT decode('-_8', 'base64url'); -- OUTPUT: \xfbff
SELECT encode('123\000\001'::bytea, 'base32hex'); -- OUTPUT: 64P36001
For Short, sortable UUIDs: combined with the cast from feature 5, this is the pattern the PG 19 docs recommend:
SELECT rtrim(encode(uuidv7()::bytea, 'base32hex'), '='); -- 26 characters
SELECT id_b32 FROM items ORDER BY id_b32 COLLATE "C"; -- keeps byte order
7. random(min, max) for dates and timestamps
PG 18 had random(min, max) only for integer, bigint and numeric. PG 19 adds date, timestamp and timestamptz versions. They return a value with min ≤ x ≤ max, so both ends are included.
-- PG 18
SELECT random('2024-01-01'::date, '2024-12-31'::date);
-- ERROR: function random(date, date) does not exist
-- PG 18 workarounds
SELECT '2024-01-01'::date + random(0, '2024-12-31'::date - '2024-01-01'::date);
SELECT now() - random() * interval '365 days'; -- upper bound never reached
-- PG 19
SELECT random('2024-01-01'::date, '2024-12-31'::date);
SELECT random(now() - interval '365 days', now());
These are handy for generating test data:
-- PG 19
INSERT INTO orders (id, order_date, created_at)
SELECT g, random('2023-01-01'::date, '2024-12-31'::date),
random(now() - interval '365 days', now())
FROM generate_series(1, 100000) g;8. range_minus_multi() and multirange_minus_multi()
The range – operator has to return a single range. When subtracting would leave two separate pieces, it raises an error. PG 19 adds set-returning functions that return each leftover piece as its own row.
-- PG 18
SELECT '[0,10)'::int4range - '[3,4)'::int4range;
-- ERROR: result of range difference would not be contiguous
-- PG 19
SELECT range_minus_multi('[0,10)'::int4range, '[3,4)'::int4range);
-- [0,3)
-- [4,10)
SELECT multirange_minus_multi('{[0,10)}'::int4multirange, '{[3,4)}'::int4multirange);
-- {[0,3), [4,10)}9. error_on_null()
error_on_null(any_element) raises an error if its input is NULL. Otherwise it returns the value unchanged. You can use it in any SQL expression as a built-in “assert not null”.
-- PG 18
SELECT error_on_null(NULL::int);
-- ERROR: function error_on_null(integer) does not exist
-- (workaround: write your own PL/pgSQL assert_not_null function)
-- PG 19
SELECT error_on_null(42); -- 42
SELECT error_on_null(NULL::int); -- ERROR
It's most useful for lookups that must succeed:
-- PG 18: a misspelled code quietly inserts NULL
INSERT INTO orders (customer_id, amount)
VALUES ((SELECT id FROM customers WHERE code = 'ACME01'), 500);
-- PG 19: the insert fails instead
INSERT INTO orders (customer_id, amount)
VALUES (error_on_null((SELECT id FROM customers WHERE code = 'ACME01')), 500);10. Polish and Esperanto stemmers, and a new Dutch stemmer
PG 19 adds full-text search configurations for polish and esperanto. It also replaces the stemmer behind dutch with a newer one; the old stemmer is still available as dutch_porter.
-- PG 18
SELECT to_tsvector('polish', 'Koty lubią mleko');
-- ERROR: text search configuration "polish" does not exist
-- PG 19
SELECT to_tsvector('polish', 'kotami') @@ to_tsquery('polish', 'kot'); -- expected: t
SELECT ts_lexize('polish_stem', 'kotami');
SELECT ts_lexize('esperanto_stem', 'hundojn');11. UPDATE / DELETE … FOR PORTION OF
A temporal table stores history, one row per item per period, with a range column showing when each row was true. PG 18 can already protect that history with temporal primary keys (WITHOUT OVERLAPS) and temporal foreign keys (PERIOD). PG 19 also lets you change part of it in one statement, and adds a new documentation chapter on temporal tables.
-- Same in PG 18 and PG 19
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE products (
product_no integer,
price numeric,
valid_at daterange,
PRIMARY KEY (product_no, valid_at WITHOUT OVERLAPS)
);
INSERT INTO products VALUES
(5, 5.00, '[2020-01-01,2022-01-01)'),
(5, 8.00, '[2022-01-01,)');
Task: Set a special price of 12.00 from 2023-09-01 to 2025-03-01.
-- PG 18: split the row yourself
BEGIN;
UPDATE products SET valid_at = '[2022-01-01,2023-09-01)'
WHERE product_no = 5 AND valid_at = '[2022-01-01,)';
INSERT INTO products VALUES
(5, 12.00, '[2023-09-01,2025-03-01)'),
(5, 8.00, '[2025-03-01,)'); -- old price copied by hand
COMMIT;
-- PG 19: one statement; the leftover rows are created for you
UPDATE products
FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01'
SET price = 12.00
WHERE product_no = 5;
Both approaches give the same result:
product_no | price | valid_at
------------+-------+-------------------------
5 | 5.00 | [2020-01-01,2022-01-01)
5 | 8.00 | [2022-01-01,2023-09-01)
5 | 12.00 | [2023-09-01,2025-03-01)
5 | 8.00 | [2025-03-01,)
Deleting a slice of history works the same way:
-- PG 19: trims or removes the overlapping rows as needed
DELETE FROM products
FOR PORTION OF valid_at FROM '2021-08-01' TO '2023-09-01'
WHERE product_no = 5;12. tid_block() and tid_offset()
Every row has a hidden ctid column that holds its physical address as (block, slot). For example, (0,3) means the row is on block 0, the first 8 KB page, in slot 3. PG 19 adds two functions that extract each part. PG 18 has no built-in way to do this.
-- PG 18: convert the tid to text, then to a point
SELECT ctid,
(ctid::text::point)[0]::bigint AS block,
(ctid::text::point)[1]::int AS slot
FROM students;
SELECT tid_block(ctid) FROM students;
-- ERROR: function tid_block(tid) does not exist
-- PG 19
SELECT ctid, tid_block(ctid) AS block, tid_offset(ctid) AS slot
FROM students;
-- ctid | block | slot
-- -------+-------+------
-- (0,1) | 0 | 1
-- (0,2) | 0 | 2| Function | Returns | Example |
| tid_block(tid) | Block number (bigint) | tid_block(‘(42,7)’::tid) → 42 |
| tid_offset(tid) | Slot within the block (integer) | tid_offset(‘(42,7)’::tid) → 7 |
13. regdatabase: database names instead of numbers
PostgreSQL identifies each database internally by a number, its OID. System views such as pg_locks and pg_stat_database store that number rather than the name. PG 19 adds regdatabase, a type that converts between the name and the number with a cast.
-- PG 18
SELECT 'postgres'::regdatabase;
-- ERROR: type "regdatabase" does not exist
-- PG 19
SELECT 'postgres'::regdatabase::oid; -- 5 (name → number)
SELECT 5::regdatabase; -- postgres (number → name)
It removes the join you used to need:
-- PG 18: join pg_database
SELECT d.datname, l.mode
FROM pg_locks l JOIN pg_database d ON d.oid = l.database;
-- PG 19: cast instead
SELECT database::regdatabase, mode
FROM pg_locks WHERE database IS NOT NULL;Conclusion
PostgreSQL 19 is not a release built around one headline feature. Its value comes from many small improvements that remove everyday friction. Workarounds you’ve typed for years now have proper built-in replacements: converting a UUID through hex text, splitting a ctid through a geometric point, joining pg_database just to read a name, and hand-splitting history rows inside a transaction.
Taken together, the thirteen features push PostgreSQL in three directions:
- Better JSON. JSON_ARRAY follows the SQL standard, jsonpath gains string methods, and IS JSON accepts domain types. You can clean, check and shape JSON in one expression.
- Safer, more explicit SQL. error_on_null() turns silent NULLs into errors. FOR PORTION OF replaces fragile multi-step history edits. The range functions return rows instead of failing.
- Smoother handling of identifiers and binary data. uuid ↔ bytea casts, base64url, base32hex, oid8, regdatabase and tid_block()/tid_offset() make IDs and raw bytes easier to work with.
Most of these changes only add capabilities, so existing applications keep working after an upgrade. Two need attention. First: JSON_ARRAY(SELECT …) now returns [] instead of NULL on empty results; and Second: Dutch full-text search uses a new stemmer. Check both before you move production to PG 19.
The practical path is to install PG 19 next to PG 18, run the examples in this post on both, and then simplify your queries with the new features over time.