Summary: In this tutorial, you will learn about PostgreSQL 18's new array and bytea function improvements including array_sort(), array_reverse(), reverse() for bytea, integer-to-bytea casting capabilities, and MIN/MAX aggregate support for arrays and composite types.

Introduction to Array and Bytea Improvements

PostgreSQL 18 introduces several useful enhancements for working with arrays and binary data (bytea). These improvements address common developer needs that previously required custom functions or complex workarounds.

The key additions include native array sorting and reversing, bytea manipulation functions, simplified integer-to-bytea conversions, and expanded aggregate support.

New Array Functions

PostgreSQL 18 adds two important array manipulation functions that developers have long requested.

array_sort() Function

The array_sort() function provides native array sorting capabilities, eliminating the need for custom sorting implementations.

-- Sort different data types
SELECT array_sort(ARRAY[3, 1, 4, 1, 5, 9, 2, 6]);
-- Result: {1,1,2,3,4,5,6,9}

SELECT array_sort(ARRAY['zebra', 'apple', 'banana']);
-- Result: {apple,banana,zebra}

array_reverse() Function

The array_reverse() function reverses the order of elements in the first dimension:

SELECT array_reverse(ARRAY[1, 2, 3, 4, 5]);
-- Result: {5,4,3,2,1}

SELECT array_reverse(ARRAY['first', 'second', 'third']);
-- Result: {third,second,first}

For multi-dimensional arrays, these functions operate only on the first dimension, so array_reverse(ARRAY[ARRAY[1,2], ARRAY[3,4]]) becomes {{3,4},{1,2}}.

Array Function Performance

Array functions work efficiently on reasonably sized arrays but may impact performance with very large arrays containing thousands of elements. The array_sort() and array_reverse() functions operate in memory, so consider the size of your arrays when using them in large datasets.

Binary Data (Bytea) Improvements

PostgreSQL 18 introduces several improvements for working with binary data, including a new reverse function and better casting capabilities.

New reverse() Function for Bytea

The reverse() function allows you to reverse the byte order of binary data:

SELECT reverse('\x12345678'::bytea);
-- Result: \x78563412

This can be used to convert between big-endian and little-endian formats, cryptographic operations that require byte reversal, and data format transformations.

Integer-to-Bytea Casting

PostgreSQL 18 introduces casting between integer types and bytea. The bytea value has the most significant byte first (big-endian format).

Basic Integer Casting

SELECT 1234::bytea;
-- Result: \x000004d2

SELECT (-1234)::bytea;
-- Result: \xfffffb2e

Different integer types produce different byte lengths - the output size is fixed based on the integer type, not the value:

SELECT 123::smallint::bytea;  -- Always 2 bytes: \x007b
SELECT 123::integer::bytea;   -- Always 4 bytes: \x0000007b
SELECT 123::bigint::bytea;    -- Always 8 bytes: \x000000000000007b

In previous versions, if you were to try to cast an integer directly to bytea, you would have to use a custom function or complex bit manipulation. Now, this is simplified with direct casting.

Whether you cast 123 or 123456, an integer::bytea conversion always produces exactly 4 bytes.

You can also cast back from bytea to integers:

SELECT '\x007b'::bytea::smallint;
-- Result: 123

This feature is useful for compact binary storage of numeric IDs, protocol implementations that require specific byte representations, and interfacing with external systems that expect binary integer formats.

MIN/MAX Aggregates for Arrays and Composite Types

PostgreSQL 18 extends MIN() and MAX() aggregate functions to work with arrays and composite types, using their natural comparison operators.

Array Aggregates

Arrays are compared element by element (lexicographically). This means {1,3} is considered less than {2,1} because the first element is smaller.

Let's see how this works with arrays:

-- Sample table with arrays
CREATE TABLE sales_data (
    product VARCHAR(50),
    monthly_sales INTEGER[]
);

INSERT INTO sales_data VALUES
    ('Laptop', ARRAY[45, 52, 38]),
    ('Mouse', ARRAY[67, 71, 58]),
    ('Keyboard', ARRAY[23, 28, 15]);

-- Find minimum and maximum sales arrays
SELECT
    MIN(monthly_sales) AS min_sales_pattern,
    MAX(monthly_sales) AS max_sales_pattern
FROM sales_data;

 min_sales_pattern | max_sales_pattern
-------------------+-------------------
 {23,28,15}        | {67,71,58}
(1 row)

This query returns the Keyboard monthly_sales value for min and the Mouse value for max because it is evaluating based on the first element of each array.

Composite Type Aggregates

Composite types are compared field by field, from left to right. Note that all field types in the composite must have comparison operators (<, >) defined, or you'll get an error.

-- Create a composite type
CREATE TYPE product_rating AS (
    average_score DECIMAL(3,2),
    review_count INTEGER
);

-- Use MIN/MAX with the composite type
SELECT
    MIN(rating) AS lowest_rating,
    MAX(rating) AS highest_rating
FROM (VALUES
    (ROW(4.5, 120)::product_rating),
    (ROW(4.2, 89)::product_rating),
    (ROW(4.8, 156)::product_rating)
) AS ratings(rating);

 lowest_rating | highest_rating
---------------+----------------
 (4.20,89)     | (4.80,156)

This query finds the lowest and highest product ratings based on average score and review count. In previous PostgreSQL versions, using MIN() or MAX() on arrays or composite types required custom aggregate functions or complex subqueries. Now, these operations are straightforward and come built-in.

Aggregate Performance

The MIN/MAX aggregates on arrays and composite types currently require sequential scans through the data. Unlike simple column aggregates, these cannot benefit from standard B-tree indexes because PostgreSQL cannot directly index arbitrary array contents or composite type comparisons.

-- This will scan all rows to find MIN/MAX arrays
SELECT MIN(monthly_sales), MAX(monthly_sales) FROM sales_data;