Query problems with Hibernate (JPA) and PostgreSQL

Question

I'm trying to use PostgreSQL as the database for Hibernate/JPA. However, I get an error when executing a simple query. My query is as follows:

SELECT DISTINCT p FROM UserProfile p ORDER BY :order

When I execute the query, I'll pass in a value like "lastLoginDate" for :order. However, I get the following exception when trying to execute the query:

ERROR org.hibernate.util.JDBCExceptionReporter  - ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 781

This query works just fine using MySQL as the database, but we'd like to move towards PostgreSQL. My overall configuration and database connection is correct, as I see the tables being created properly. Is there something I'm missing? Is there a configuration property I need to set? Any help you can give would be MUCH appreciated. Thanks!

Answer 1

Postgresql prohibits that query because it is ambiguous:

there's actually a definitional reason for it.  Consider

        SELECT DISTINCT x FROM tab ORDER BY y;

For any particular x-value in the table there might be many different y
values.  Which one will you use to sort that x-value in the output?

It's not very clear what you want (an example?), but if you intend to sort all records by ':order' column, and then remove duplicated values, you can do that first with DISTINCT ON: , and then reorder:

 SELECT p FROM  
    ( SELECT DISTINCT ON (p) * from UserProfile ORDER BY p , :order) 
    AS UserProfileUniq 
  ORDER BY :order ;