How to set a postgres function parameter using Native query?

Asked

Viewed 13 times

0

Good afternoon!

I created a Native query that uses a postgres function date_part(text, timestamp). My code:

String sql = "SELECT "
                + "(SELECT coalesce(count(*), 0) FROM public.cliente) AS totalClientes, "
                + "(SELECT coalesce(count(*), 0) FROM cliente WHERE date_part('year', dt_cadastro) = date_part('year', :ano) ) AS totalNovosClientes";
                
Query query = entityManager.createNativeQuery(sql);
        query.setParameter("ano", "2021-01-01");

The problem is that when setting this parameter year gives an error saying that I have to clarify the Casts. Other ways I tried but also didn’t work:

date_part('year', TIMESTAMP :ano)
query.setParameter("ano", "2021-01-01");
query.setParameter("ano", java.sql.Timestamp.valueOf("2021-01-01 10:10:10.0"), TemporalType.TIMESTAMP );

How I could set the value in the function using the query.setParameter correctly?

  • if you are going to use date, the query should not be like this date_part('year', ':ano'),?

  • @Ricardopunctual tried so tbm didn’t work

  • Could show which error it displays?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.