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

How to ignore quotes for nested ARRAY in a PostgreSQL query in a Spring Data @Query

The question may seems stupid but I am stuck on executing that kind of query:

UPDATE user SET productArr = ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]] WHERE id = 1;

within a @Query Spring Data.

I defined my repository method like:

    @Modifying
    @Query(value = """
            UPDATE user
            SET productArr = :productArr WHERE id = :userId
            """, nativeQuery = true)
    void updateProductArr (@Param("productArr") String productArr, @Param("userId") Long userId);

I set String productArr="ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]]"
The error is:

org.postgresql.util.PSQLException: ERROR: column "productArr" is of type text[] but expression is of type text

I tried to change the type of productArr from String to String[] or List<String> without success.

I also tried this solution: UPDATE user SET productArr = REPLACE(:productArr,'"','') WHERE id = :userId but same result.

Please note that when I run that query on a database console it works well.
Thank you in advance for your help.

Tags:

2

Answers


  1. 0

    When you update your data using this query:

    UPDATE user SET productArr = ARRAY[ARRAY[2,2],ARRAY[3,2],ARRAY[1,3]] WHERE id = 1;

    Postgres automatically converts you integer array to string array, because as I understand the productArr field is type of text[]

    You can filter this field using this correct SQL queries:

    select * from users
where productArr = ARRAY[ARRAY['2','2'],ARRAY['3','2'],ARRAY['1','3']]

    or

    select * from users
where productArr = '{{2,2},{3,2},{1,3}}'::text[]

    In your spring project you have two ways do this.

    1. Use array type for input parameters, don’t use string type
    2. You can use string type for input parameters, but then you should cast type inside the native query.

    For example:

    @Modifying
@Query(value = """
        UPDATE user
        SET productArr = (:productArr)::text[] WHERE id = :userId
        """, nativeQuery = true)
void updateProductArr (@Param("productArr") String productArr, @Param("userId") Long userId);

    And use:

    String productArr = "{{2,2},{3,2},{1,3}}"
    Login or Signup to reply.
  2. 0

    Your problem is brought on by a type mismatch between the PostgreSQL array type and the productArr argument of your Spring Data query. To fix this, modify the type of the productArr parameter in your method signature to String[] and explicitly define the parameter type using the ::text[] cast in your query. This guarantees for the update query to run effectively.

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

Back To Top
Search