How to change brackets for postgresql?

Question

I want to retrieve data from this query. But this query works if col has only one value. If there is 2 or more value it doesn't work.

@Query(value="SELECT * FROM art where color @> ARRAY[:col]",
            nativeQuery=true)
    List<Recept> findBy( @Param("col") Set<Integer> col);

By hibernate I see the query. this one works fine.

Hibernate: SELECT *   FROM  art  where color @> ARRAY[(?)]

While this doesn't work. ERROR: operator does not exist: integer[] @> record[]

Hibernate: SELECT *   FROM  art  where color @> ARRAY[(?, ?)]

I think the problem is in brackets.

I've never written a query like this before in PostGres, but if you look carefully at the error message, it thinks `ARRAY[(?, ?)]` is a `record[]` — Robert Harvey, Aug 03 '21 at 14:32
Probably because it didn't expect you to hand it a `record[]`. — Robert Harvey, Aug 03 '21 at 14:33
idk why it is presented as a record since I pass set integer — , Aug 03 '21 at 14:34
Or you passed it a two dimensional array index. Either way, it surely doesn't match `ARRAY[(?)]` — Robert Harvey, Aug 03 '21 at 14:36
no i didn't use two-dimensional array neither postgres or entity — , Aug 03 '21 at 14:38
Well, you're going to have to figure it out. Clearly PostGres is expecting something of the form `ARRAY[(?)]`, not `ARRAY[(?, ?)]` — Robert Harvey, Aug 03 '21 at 14:39
color data type is integer[] . I checked in pgAdmin Array[1,2] works fine with the same query — , Aug 03 '21 at 14:41
Then you're leaving something out of your question that we need to see. Aren't those question marks parameters? How are you populating them? — Robert Harvey, Aug 03 '21 at 14:42
I would like to see it like this SelECT * FROM art where color @> ARRAY[?, ?] — , Aug 03 '21 at 14:44
You want `array[?,?]` - the expression `(?,?)` is a single value which is an anonymous record type (with two fields) — , Aug 03 '21 at 14:51
well I checked in pgadmin SelECT * FROM art where color @> ARRAY[1, 2] it works fine while in the repository SelECT * FROM art where color @> ARRAY[(1, 2)] it gives error — , Aug 03 '21 at 14:53
As I wrote: `ARRAY[(1, 2)]` is something completely different than `ARRAY[1, 2]`. The first expression is an array with a single element, the second is an array with two integer. Just get rid of the parentheses. — , Aug 03 '21 at 16:58

score 1 · Answer 1 · answered Aug 03 '21 at 16:31

1

You may change the code to this

@Query(value="SELECT * FROM art where color in :col",
            nativeQuery=true)
    List<Recept> findBy( @Param("col") Set<Integer> col);

So the resultant query will be

SELECT *   FROM  art  where color in (1,2,3)

Where (1,2,3) is a list of scalar values used along with the IN operator

TD;DR:

Your problem is that you are passing as second argument to the @> operator a list of rows and not a list of integers. As horse_with_no_name states in the comments, you are generating a wrong query trying to compare things of different nature.

answered Aug 03 '21 at 16:31

Victor

3,841
2
37
63

it didn't work ERROR: operator does not exist: integer[] = integer. Also I need a list of rows since I want to get art with colors red AND yellow(that's why color column is int[]). – Aug 04 '21 at 10:10
@FireHe4d look this is exacly a another question of another persona that faces the same problem https://stackoverflow.com/questions/40020972/bind-array-param-to-native-query, so probably you will have a **common accepted answer** for both problems. Also consider to dig further into docs like https://thorben-janssen.com/mapping-arrays-with-hibernate/#Map_it_as_a_Native_Database_Array AND https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query – Victor Aug 06 '21 at 13:22
another folk facing the same issue https://stackoverflow.com/questions/55232828/how-to-using-like-array-of-string-hibernate – Victor Aug 06 '21 at 13:36

How to change brackets for postgresql?

1 Answers1