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 <= ? 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 <= ?)