The column name id was not found in this ResultSet

Question

@Query(value = "SELECT s FROM logines.work_hour s WHERE TO_TIMESTAMP(s.start_time,  'YYYY-MM-DD HH24:mi')\\:\\:timestamp BETWEEN :start_time\\:\\:timestamp AND now()\\:\\:timestamp", nativeQuery = true)
List<WorkHour> findByStartTime(@Param("start_time") String startTime);

org.postgresql.util.PSQLException: The column name id was not found in this ResultSet.

What might be an issue?

Answer 1

I got the same problem. It seems that in some scenarios the result columns can not be mapped to the output entity if we use table alias in SELECT statement.

SELECT t FROM my_table t WHERE ....;

The same query was working in one cases and failing in others. I found out that the problem goes away if we use a star * instead of the alias:

SELECT * FROM my_table t WHERE ...;