Incorrect mapping of List<T> to PostgreSQL composite array column

[G0dC0der]

Spring Boot 4.0.5, Java 25, JDBC.

Hi,

I ran into an issue when trying to map a PostgreSQL composite array column to a collection property in Spring Data JDBC. Given the following schema:

CREATE TYPE my_element AS (
    value1 INT,
    value2 TEXT
);

CREATE TABLE my_table (
    id BIGSERIAL PRIMARY KEY,
    elements my_element[]
);

import java.util.List;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

@Table("my_table")
public record MyTable(@Id Long id, @Column("elements") List<MyElement> elements) {

    public record MyElement(int value1, String value2) {}
}

import org.springframework.data.repository.CrudRepository;

public interface MyTableRepository extends CrudRepository<MyTable, Long> {
}

The integration test:

    @Test
    void postgresCompositeArraysAreMappedCorrectly() {
        var myTable = new MyTable(null, List.of(new MyElement(111, "value111"), new MyElement(222, "foo222")));
        var saved = myTableRepository.save(myTable); //Throws exception
        //Assert later
    }

The call to save throws exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO "my_element" ("elements", "my_table_key", "value1", "value2") VALUES (?, ?, ?, ?)]

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:134)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:102)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:111)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1549)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:689)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:711)
	at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1043)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:379)
	at org.springframework.data.jdbc.core.convert.InsertStrategyFactory$DefaultBatchInsertStrategy.execute(InsertStrategyFactory.java:89)
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:151)
	at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.executeBatchInsert(JdbcAggregateChangeExecutionContext.java:116)
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:89)
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.lambda$executeSave$0(AggregateChangeExecutor.java:60)
	at org.springframework.data.relational.core.conversion.BatchedActions$InsertCombiner.lambda$forEach$1(BatchedActions.java:170)
	at java.base/java.util.HashMap.forEach(HashMap.java:1430)
	at org.springframework.data.relational.core.conversion.BatchedActions$InsertCombiner.lambda$forEach$0(BatchedActions.java:170)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:186)
	at java.base/java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:357)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:571)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:560)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:153)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:176)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:265)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:632)
	at org.springframework.data.relational.core.conversion.BatchedActions$InsertCombiner.forEach(BatchedActions.java:169)
	at org.springframework.data.relational.core.conversion.BatchedActions.forEach(BatchedActions.java:79)
	at org.springframework.data.relational.core.conversion.SaveBatchingAggregateChange.forEachAction(SaveBatchingAggregateChange.java:71)
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.executeSave(AggregateChangeExecutor.java:60)
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.performSave(JdbcAggregateTemplate.java:587)
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.save(JdbcAggregateTemplate.java:219)
	at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.save(SimpleJdbcRepository.java:74)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:565)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:278)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:169)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:545)
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:290)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:690)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:171)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:146)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:133)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:371)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:130)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:135)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:222)
	at jdk.proxy1/jdk.proxy1.$Proxy158.save(Unknown Source)
	at se.myname.myapp.persistence.repository.CourtCaseRepositoryIT.postgresArraysAreMappedCorrectly(CourtCaseRepositoryIT.java:32)
Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO "my_element" ("elements", "my_table_key", "value1", "value2") VALUES (('1'::int8), ('0'::int4), ('111'::int4), ('value111')) was aborted: ERROR: cannot open relation "my_element"
  Detail: This operation is not supported for composite types.
  Position: 13  Call getNextException to see other errors in the batch.
	at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2561)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:672)
	at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:892)
	at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916)
	at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1778)
	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:565)
	at net.ttddyy.dsproxy.proxy.StatementProxyLogic.performProxyLogic(StatementProxyLogic.java:287)
	at net.ttddyy.dsproxy.proxy.ProxyLogicSupport.proceedMethodExecution(ProxyLogicSupport.java:103)
	at net.ttddyy.dsproxy.proxy.StatementProxyLogic.invoke(StatementProxyLogic.java:119)
	at net.ttddyy.dsproxy.proxy.jdk.PreparedStatementInvocationHandler.invoke(PreparedStatementInvocationHandler.java:37)
	at jdk.proxy1/jdk.proxy1.$Proxy133.executeBatch(Unknown Source)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$getPreparedStatementCallback$0(JdbcTemplate.java:1609)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:670)
	... 48 more
Caused by: org.postgresql.util.PSQLException: ERROR: cannot open relation "my_element"
  Detail: This operation is not supported for composite types.
  Position: 13
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2875)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2560)
	... 63 more

This indicates that the framework interprets List as a child collection and attempts to persist it into a separate table named after the element type. However, in this case my_element is a PostgreSQL composite type, not a table. PostgreSQL then fails with:

ERROR: cannot open relation "my_element"
Detail: This operation is not supported for composite types.

This demonstrates that Spring Data JDBC currently has no way to map a collection property to a single column containing a PostgreSQL composite array (my_element[]). Instead, it always treats List as a relational association. From a PostgreSQL perspective, mapping a collection of structured values into a single column is a valid and common pattern, but it seems unsupported in the current mapping model.

My questions are:

• Is mapping a collection property to a single column (e.g. PostgreSQL composite arrays) considered out of scope for Spring Data JDBC?
• If not, what is the recommended extension point to support this (custom JdbcConverter, overriding MappingJdbcConverter, or something else)?
• Would the team consider adding an official extension mechanism for handling vendor-specific structured column types like this?
• Does the new JdbcClient support this?

I’d be happy to contribute a PR, but would like guidance on the intended direction before proceeding.

[G0dC0der]

Summary: Mapping PostgreSQL Composite Type Arrays in Spring Data JDBC

The core challenge was preventing Spring Data JDBC from treating a List<MyElement> as a one-to-many relationship (attempting to join/insert into a separate table) instead of a single column array (my_element[]).

❌ Failed Approaches (The "Why" they didn't work)

• Custom Class extending ArrayList:
  I tried to bypass the relationship logic by creating a dedicated class ElementList extends ArrayList<MyElement>. Even with this, Spring's reflection identified the Collection hierarchy and defaulted to relationship mapping.

• Manual SimpleType Registration:
  I attempted to force Spring to see our custom list(ElementList) as a column by overriding jdbcMappingContext() and explicitly adding the class to the SimpleTypeHolder. Despite being registered as a "simple type", the custom converters were never triggered because the internal RelationalMappingContext had already flagged the type as a nested entity/collection during the initial scan.

• Direct JdbcValue in the Record:
  Using record MyTable(@Id Long id, JdbcValue elements) stopped the relationship errors, but shifted the failure to the driver level. Since JdbcValue is a container and not a logic provider, passing a raw Java list into it resulted in a PSQLException because the PostgreSQL driver does not know how to serialize custom Java Records into composite type literals.

---

✅ Successful Workaround (The Solution)

The only way to consistently bypass the relationship engine and trigger the conversion layer was to hide the collection inside a wrapper class that does not implement Collection or List.

1. The Wrapper Pattern
By using a simple wrapper, we break the automatic collection-scanning:

@Table("my_table")
public record MyTable(@Id Long id, @Column("elements") MyElements elements) {

    public record MyElement(int value1, String value2) {}

    public record MyElements(List<MyElement> value) {}
}

Since MyElements is now an "unknown" object to Spring, it finally consults the JdbcCustomConversions. I was then forced to implement:

• A Writing Converter: Using jdbcOperations to access the native Connection, creating a java.sql.Array of PGobjects. Each PGobject is manually formatted as a PostgreSQL record literal (val1,val2).
• A Reading Converter: To parse the incoming org.postgresql.jdbc.PgArray and its internal java.sql.Struct objects back into our MyElements wrapper.

Key Insight: In Spring Data JDBC, if you want to avoid the "One-to-Many" mapping for a list, you must wrap it in a non-collection class and handle the low-level JDBC serialization manually via JdbcValue and PGobject.

This approach is far from ideal as it is highly time-consuming and error-prone; it forces developers to abandon the high-level abstraction of the framework and manually handle low-level JDBC serialization (like string-formatting record literals and managing SQL connections), which defeats much of the purpose of using an ORM/Mapping framework in the first place.

The converters, for reference:

    // The writer 

    @Override
    public JdbcValue convert(MyElements source) {
        return jdbc.execute((Connection conn) -> {
            Object[] pgObjects = source.value().stream()
                .map(e -> {
                    try {
                        PGobject row = new PGobject();
                        row.setType("my_element");
                        row.setValue("(" + e.value1() + "," + e.value2() + ")");
                        return row;
                    } catch (SQLException ex) {
                        throw new RuntimeException(ex);
                    }
                })
                .toArray();

            Array sqlArray = conn.createArrayOf("my_element", pgObjects);
            return JdbcValue.of(sqlArray, JDBCType.ARRAY);
        });
    }

    //The reader 

    @Override
    public MyElements convert(PgArray pgArray) {
        try {
            Object[] rawElements = (Object[]) pgArray.getArray();
            if (rawElements == null) {
                return new MyElements(List.of());
            }

            List<MyTable.MyElement> list = new ArrayList<>();

            for (Object obj : rawElements) {
                Struct struct = (Struct) obj;

                Object[] attributes = struct.getAttributes();

                list.add(new MyTable.MyElement(
                    (Integer) attributes[0],
                    (String) attributes[1]
                ));
            }

            return new MyElements(list);

        } catch (SQLException e) {
            throw new RuntimeException("ops failed", e);
        }
    }

[christophstrobl]

Thank you for bringing this top up and posting your findings on this issue. This issue seems to go into the same direction as #482 (also proposing the wrapper type workaround, which seems to never have seen a follow up to actually provide a helper for the suggested wrapper).