0

In a PG 12 database I have the reservations table with the reserved_between tsrange column.

For a specific analysis, I need to know the ending of the last reservation (later in time) and teh following query does just that.

SELECT MAX(upper(reservations.reserved_between))
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG>

Where <MYID> and <MYTAG> are the params needed to identify the analysis.

Now, in case there a no reservations, I need to return the as_early_as (datetime) value of the batch the analysis refer to.

I think COALESCE could be a good option for the job. Thus, I have updated the quesry as follows.

SELECT COALESCE(MAX(upper(reservations.reserved_between)), batches.as_early_as)
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
JOIN dev.batches ON analyses.batch_id = batches.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG> 

However, when I run I get

ERROR: column "batches.as_early_as" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ALESCE(MAX(upper(reservations.reserved_between)), batches.as...

And I'm not sure how to fix it and the documentation doesn't help me too much.

What should I change in my query?

1
SELECT COALESCE(MAX(upper(reservations.reserved_between)), MAX(batches.as_early_as))
1
  • Thanks Jasen for the help.
    – Sig
    Feb 13 '20 at 9:59

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.