So I am trying to write a native named query for JPA (JPQL) to INSERT multiple rows of data in one go, and get back all id's of the inserted records (that don't conflict with unique constrain on the primary key). I am using a Postgres database.
Database looks like this: SELECT * FROM table;
id | sent
---+------
1 | f
(1 row)
What I'd like to achieve is this PSQL statement:
INSERT INTO table VALUES (1, false),(2, false) ON CONFLICT DO NOTHING RETURNING id;
Which should return:
id
---
2
(1 row)
So my initial idea was to make a native query in JPA:
@Repository
interface NotificationRepository : JpaRepository<Foo, Int> {
@Modifying
@Query("INSERT INTO Foo VALUES (:foos) ON CONFLICT DO NOTHING RETURNING id", nativeQuery = true)
fun addAllAndReturnInserted(foos: List<Foo>): List<Int>
}
Which works for one value in the list, but for multiple values it will create a query like:
Hibernate: INSERT INTO Foo VALUES (?, ?) ON CONFLICT DO NOTHING RETURNING id
And it throws an exception:
PSQLException: ERROR: column "sent" is of type boolean but expression is of type int
Is there a way to transform the list into separate perentheses like (?), (?) instead of in one (?, ?) ??
