ERROR: 42803: column "wage_histories.wage" must appear in the GROUP BY clause or be used in an aggregate function I'm trying to migrate an application from SQLite to PostgreSQL, and now I have reached the point of re-expressing the queries that are valid in one and invalid in the other.

I have a table:

CREATE TABLE wage_histories (
  uid INTEGER REFERENCES users ( id ) ON DELETE CASCADE NOT NULL,
   start TEXT NOT NULL,
   wage INTEGER NOT NULL,
   PRIMARY KEY ( uid, start )
);


It represents the wage of a user, over time. So, the wage of a user on a particular date is the value of the wage column in the row with our chosen uid and the highest start that is less than our chosen date.

I was able to express this in SQLite with the following query:

SELECT wage, MAX(start) FROM wage_histories WHERE start &lt;= ? AND uid = ?                                                                                                                                                                    


However in PostgreSQL this gives me the following error:                                                                                                                                                                                    

ERROR:  42803: column "wage_histories.wage" must appear in the GROUP BY clause or be used in an aggregate function


Is there something more succinct than the following?

SELECT wage FROM wage_histories WHERE uid = ? AND start = (SELECT MAX(start) FROM wage_histories WHERE uid = ? AND start &lt;= ?)
If you just need one row: 

SELECT wage 
FROM wage_histories 
WHERE uid = ? 
  AND start &lt;= ? 
ORDER BY start DESC
LIMIT 1