org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

Question

I am using JPA with spring Boot, and Postgres. I am trying to run a native query using a repository that extends org.springframework.data.repository.CrudRepository. The query looks like this:

public interface MyRepository extends CrudRepository<MyObject, String> {    
    ...
    @Query(value = "SELECT * FROM objects WHERE metadata @> 
    jsonb_build_object('tenant', :tenant , 'objectType', :objectType )", nativeQuery = true)
    List<MyObject> findAllBy(@Param("tenant") String tenant, @Param("objectType") String objectType);
    ...
}

When I run the query I get the error:

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

I went over many similar cases but can't get this query to work in this or similar variations.

Table create statement:

create table objects
(
    key varchar(256),
    tenant varchar(256),
    metadata jsonb,
    data jsonb
);

Any ideas what I'm doing wrong?

Additional variation I have tried (this time with a pageable object):

@Query(value = "SELECT * FROM objects WHERE metadata @> jsonb_build_object(\\'tenant\\', ?1 , \\'objectType\\', ?2) ORDER BY ?#{#pageable}",
        countQuery = "SELECT count(*) FROM objects WHERE metadata->>\\'tenant\\' = ?1 and metadata->>\\'objectType\\' = ?2",
        nativeQuery = true)
Page<OSObject> findAllBy(String tenant, String objectType, Pageable pageable);

This results in: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

I'm still in the dark...

Additional variation I have tried:

Jacobs2000
– Jacobs2000

2017-10-18 14:21:42 +00:00
Commented Oct 18, 2017 at 14:21 — Jacobs2000
– Jacobs2000, Commented Oct 18, 2017 at 14:21

wvh · Accepted Answer · 2018-08-07 16:12:55Z

3

I think this is because jsonb_build_object accepts variadic arguments. You need a typecast to tell Postgresql the data type of the keys and values provided as variables (text or ints or whatever else), otherwise it doesn't know what data type to use inside the json.

jsonb_build_object('key', $1::text)

I don't know anything about Java and Spring, but I imagine it tries to use a prepared statement with dollar variables under the hood. Try to cast your variables with ::text.

answered Aug 7, 2018 at 16:12

wvh

1911 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related