CODEWITHSUNDEEP
sqlpostgresqlsql-order-bydistinct
user3275784
user3275784

Reputation: 467

SELECT DISTINCT, ORDER BY expressions must appear in select list - error when migrating from Oracle to Postgres

I'm in the process of migrating our database from Oracle to Postgres. We have a number of hardcoded queries in our spring boot application that now break as result of this migration, and I'm having to fix them. One of them is the following:

String sql = "SELECT DISTINCT * FROM TABLE_A A " +
    "INNER JOIN TABLE_B B ON A.BIC = B.BIC " +
    "WHERE B.CUST_NUMBER=?" +
    "AND A.LOCATION=?" +
    "AND B.STATUS=?" +
    "AND B.LIVE=TRUE" +
    "ORDER BY A.TYPE, A.PARTY";

This query runs fine against oracle, but not against postgres. When running against postgres, I get the following error: SELECT DISTINCT, ORDER BY expressions must appear in select list

After doing some reading and even seeing similar questions about the same error, I am none the clearer on how I fix this for the above query. Removing the DISTINCT clause will fix the error, but obviously it is not the same query then, so that is a no go.

If anyone can offer some advice, it would be greatly appreciated.

Upvotes: 0

Views: 732

Answers (1)

user330315
user330315

Reputation:

Your query works in Postgres 13 and later. If you start a new project, then you should use the most recent version, rather than starting with a slightly outdated version

In older versions, you can wrap it in a derived table to get around that.

select *
from (
  SELECT DISTINCT * 
  FROM TABLE_A A 
    JOIN TABLE_B B ON A.BIC = B.BIC 
  WHERE B.CUST_NUMBER=?
    AND A.LOCATION=?
    AND B.STATUS=?
    AND B.LIVE=TRUE
) a
ORDER BY A.TYPE, A.PARTY

Postgres 12 example

Postgres 14 example

Upvotes: 2

Related Questions