Postgre query error: ERROR: syntax error at or near "WHERE"

Question

I was working with mysql in this project and the query worked normally. At the moment, I am migrating to postgresql. However, the query stopped working and returned the following error:

ERROR: syntax error at or near "WHERE"

My code

@Query(value = "SELECT b.nome FROM escala a INNER JOIN medico b " 
                +"INNER JOIN ordem c ON c.medico_crm = b.crm AND a.id = c.escala_id " 
                +"WHERE month(a.data) = :mes AND year(a.data) = :ano AND "
                + "b.categoria = :categoria group by b.nome", nativeQuery=true)
Set<String> getNomeMedicos(@Param("mes") int mes, @Param("ano") int ano, @Param("categoria") String categoria);

Answer 1

I managed to fix it. I’ll explain to anyone who has the same problem. There was no syntax error, but the query structure was wrong. Above, note that the select refers to the medical table b, however the from is from the scale table a. To be clearer, follow the corrected code:

SELECT a.nome from medico a INNER JOIN ordem b INNER JOIN escala c ON a.crm = b.medico_crm AND b.escala_id = c.id 
WHERE day(c.data) = :dia AND month(c.data) = :mes AND year(c.data) = :ano AND c.turno = :turno;

Answer 2

The commands Month and Year are Mysql operators

Postgres has a command called

EXTRACT(oque FROM quem)

So I think your code should look like this:

@Query(value = "SELECT b.nome FROM escala a INNER JOIN medico b " 
            +"INNER JOIN ordem c ON c.medico_crm = b.crm AND a.id = c.escala_id " 
            +"WHERE EXTRACT(MONTH FROM a.data) = :mes AND EXTRACT(YEAR FROM a.data) = :ano AND "
            + "b.categoria = :categoria group by b.nome", nativeQuery=true)
Set<String> getNomeMedicos(@Param("mes") int mes, @Param("ano") int ano, @Param("categoria") String categoria);

This command is responsible for getting date values where the first parameter will be what you want to get and the second from where you want to get it. Above I illustrated the first parameter calling you oQue and the second of deQuem.