Selecting values from list parameter

user_6333944 0
Viewed 354

I'm trying to write a method which for given list of String values will return list of String values not existing in some column of the some table in the db.

So, for example, if we have table t with column c and inserted values "1", "2" and "3", and we pass list ["1", "2", "4", "5"] to the method, it should return ["4", "5"].

I tried something like this:

@Query(value = "select t.id from :list as t(id) left join table_name s " +
        "on s.column_name= t.id where s.column_name is null", nativeQuery = true)
    List<String> findNonExistingValues(List<String> list);

but it is not working as i hoped giving the error: syntax error at or near "$1"

Thanks in advance.

jpa postgresql java
2 Answers

You can't select ... from variable, only from table or other select.

You can create select that returns your list, but it will look ugly:

select "a"
union all
select "b"
union all
select "c"

May not work with your database.

I suggest you to return list that exist in database and substract one from another in Java code.

user_36569040

Postgres easily handles what you are looking to do. To accomplish this requires 4 steps:

  1. Transform your list to a string of comma separated values.
  2. Transform that string to an Array.
  3. Transform that array into Individual values.
  4. Select the Individual values where they do not exist in the table.

The first step you will have to do in the app. The rest can be handled with a single SQL statement. It uses the functions string_to_array to create the array and 'pipes' that out put into the unnest function, then uses NOT EXIST for those values not in the specified table. The result: (see example here)

with tgt_list (tval) as
     (select unnest(string_to_array('1,2,4,5', ','))::integer)
select tval 
  from tgt_list 
 where not exists 
       (select null 
          from tbl 
         where tval = tbl.val
       ) ;

You just need the above replacing the hardcored value with a parameter. Sorry I do not know your ORM or Java.

Note: Your DBeaver test actually had nothing to do with DBeave itself. Postgres interrupted the VALUES clause and created independent value for parenthesized expression.

user_76238560
Related