0

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

If you just need one row:

SELECT wage 
FROM wage_histories 
WHERE uid = ? 
  AND start <= ? 
ORDER BY start DESC
LIMIT 1
2
  • This works. but I'm having problems with adapting it to the places where I need to get the full table of users – pjstirling Jun 11 '20 at 14:27
  • 1
    @pjstirling: if you need a different query, you should have asked a different question – a_horse_with_no_name Jun 11 '20 at 16:23
1

You want something like:

SELECT wage FROM
(
  SELECT 
   ROW_NUMBER() OVER (PARTITION BY uid, ORDER BY start DESC) as rn,
   wage
   WHERE start > ?
   AND uid = ?
) AS tab
WHERE rn = 1;

This will work nicely for all users if you take out the AND uid = ? clause, which, if I've understood your reply to @a_horse_with_no_name, is what you want?

Please tell us that you're not storing DATEs as TEXT?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.