Question 404622 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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)
...
    Login or Signup to reply.
  2. 0

    add a positional argument to max(expression) in PostgreSQL
    use a CASE statement

    SELECT max(CASE WHEN $1 IS NULL THEN  my_real_column ELSE $1 END)

FROM my_table;

    SELECT max(GREATEST(my_real_colomn,$1))

FROM my_table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search