Search code examples
sqlpostgresql

How to add parameter(s) to an aggregate's value expression in Postgres?

I have a query where I need to add a positional argument into a call to max(expression).

SELECT max(my_real_column) FROM my_table

I've tried the concatenation operator, e.g. my_real_column || $1, but that yields an operator error:

SELECT max(my_real_column || $1) FROM my_table

The postgres docs show that an aggregate function can take multiple expressions, so I tried this:

SELECT max(my_real_column, SELECT $1) FROM my_table

But this similarly does not work.

How can I accomplish this with standard SQL?

Solution

  • max only accepts one value. To calculate the maximum value of max(..) and a fixed value, use the greatest() function and pass the max(..) and the fixed value:

    SELECT greatest(max(my_real_column), $1)
...