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!

possible duplicate of postgreSQL group by different from mysql? — Milen A. Radev
– Milen A. Radev, Commented May 12, 2010 at 18:02

leonbloy · Accepted Answer · 2010-05-12 18:04:36Z

7

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 ;

edited May 12, 2010 at 18:04

answered May 12, 2010 at 17:47

leonbloy

76.4k22 gold badges149 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shadowman Over a year ago

So what I was doing was something like this: * Create a named query (which corresponds to the query I posted) * I set the parameters, including the ':order' parameter which indicated which column I wanted to sort on My expectation was that my query would get translated to something like: SELECT DISTINCT p FROM UserProfile p ORDER BY lastLoginDate; That is the behavior with MySQL. I'll run the code again with increased logging and post what I get. I'll also try leonbloy's method. Someone also recommended removing DISTINCT if I don't need it. Do you think that will work?

leonbloy Over a year ago

To understand if it will work, we must understand the result you expect, with some example (with data).

Collectives™ on Stack Overflow

Query problems with Hibernate (JPA) and PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related