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.
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