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

Passing parameter value in native query where it will be surrounded by single quotation marks – Postgresql

I’ve spent a lot of time trying to figure out how to pass a parameter value to a native sql query. The value in the resulted query is surrounded with single quotation marks. That must be the main issue I think.

The following query works if the parameter value is hard-coded. I’ve excluded unrelated parts from it:

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - interval '70' minute
...
""";

However when I tried to pass the value:

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - interval ':x' minute
...
""";

var searchQuery = em.createNativeQuery(query, BikeEntity.class);
searchQuery.setParameter("x", 70);

I got the error:

java.lang.IllegalArgumentException: Could not locate named parameter [x], expecting one of []

Without quotation marks:

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - interval :x minute
...
""";

The error was:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 205

I’ve also tried different recommendations in this post Native query with named parameter fails with "Not all named parameters have been set"

but without success

Using
Java 17
SpringBoot 2.7.6
PostgreSQL

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Meanwhile I solved the problem more radically by parameterizing the right operand of < entirely:

    var query = """
...
and ss.status_date < :x
...
""";

var searchQuery = em.createNativeQuery(query, BikeEntity.class);
var calendar = Calendar.getInstance();
calendar.add(Calendar.MINUTE, - 70);
searchQuery.setParameter("x", calendar.getTime());
return searchQuery.getResultList();

  2. 0

    Just pass your parameter as string like

    searchQuery.setParameter("x", "70 minutes");

    And cast it as PostgreSQL interval type as

    var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - :x::interval
...
""";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search