PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to map Postgres VARCHAR[][] to String[][] via Spring JPA

I’ve created a table

CREATE TABLE test_table (
  id VARCHAR(36) PRIMARY KEY,
  text_arr VARCHAR[][] NOT NULL
)

and an entity

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Entity
@Table(name = "test_table")
public class TestTable {
  @Id
  private String id;
  private String[][] text_arr;
}

also a repository

public interface TestTableRepository implements JpaRepository<TestTable, String> {}

This configuration doesn’t work. I also tried by example from this post https://vladmihalcea.com/multidimensional-array-jpa-hibernate/
like this

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Entity
@Table(name = "test_table")
public class TestTable {
  @Id
  private String id;
  @Type(
      value = StringArrayType.class,
      parameters = @Parameter(
              name = "sql_array_type",
              value = "varchar"
      )
)
@Column(name = "text_arr", columnDefinition = "varchar[][]")
  private String[][] text_arr;
}

my service is able to start, but when I execute

repository.save(entity)

it fails with

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERROR: incorrect binary data format in bind parameter 1
  Where: unnamed portal parameter $1] [insert into test_table (text_arr,id) values (?,?)]; SQL [insert into test_table (text_arr,id) values (?,?)]] with root cause

org.postgresql.util.PSQLException: ERROR: incorrect binary data format in bind parameter 1
        Where: unnamed portal parameter $1

I’m using Spring Boot 3.3.3

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After some investigation, I figured out that the problem was not in the configuration but in Postgres. Regarding the documentation

    Multidimensional arrays must have matching extents for each dimension.

    So if you have an array like

    new String[][] {{"str1"}, {"str2", "str3"}}

    it will fail. As the solution, I stored an array as jsonb. FYI here is an example of a valid mapping https://github.com/vladmihalcea/hypersistence-utils/blob/984465ec3af4b564ee321c39567400f54fef321f/hypersistence-utils-hibernate-63/src/test/java/io/hypersistence/utils/hibernate/type/array/MultiDimensionalStringArrayTypeTest.java#L116


  2. 0

    You also need to define a Hibernate type to be used over the entity via @TypeDef and then you use it via @Type.

    Update to this:

    @AllArgsConstructor
@NoArgsConstructor
@Getter
@Entity
@Table(name = "test_table")
@TypeDef(name = "test_table_array", typeClass = StringArrayType.class)
public class TestTable {
  @Id
  private String id;

  @Type(
    type = "test_table_array",
    parameters = @Parameter(
        name = "sql_array_type",
        value = "VARCHAR"
    )
  )
  @Column(name = "text_arr", columnDefinition = "VARCHAR[][]")
  private String[][] text_arr;
}

    See if this helps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search