How to get min/max of two integers in Postgres/SQL?

Question

How do I find the maximum (or minimum) of two integers in Postgres/SQL? One of the integers is not a column value.

I will give an example scenario:

I would like to subtract an integer from a column (in all rows), but the result should not be less than zero. So, to begin with, I have:

UPDATE my_table
SET my_column = my_column - 10;

But this can make some of the values negative. What I would like (in pseudo code) is:

UPDATE my_table
SET my_column = MAXIMUM(my_column - 10, 0);

Relatedly you can create a union data set and then max that, in sql-server at least https://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-ne — Kzqai, Mar 28 '20 at 18:31

score 480 · Accepted Answer · edited Jan 08 '15 at 15:35

480

UPDATE my_table
SET my_column = GREATEST(my_column - 10, 0);

edited Jan 08 '15 at 15:35

answered May 29 '10 at 19:42

Mark Byers

score 22 · Answer 2 · edited Apr 21 '16 at 15:11

22

You want the inline sql case:

set my_column = case when my_column - 10 > 0 then my_column - 10 else 0 end

max() is an aggregate function and gets the maximum of a row of a result set.

Edit: oops, didn't know about greatest and least in postgres. Use that instead.

edited Apr 21 '16 at 15:11

ThiefMaster

answered May 29 '10 at 19:41

Donnie

17

This version is useful if you're restricted to standard SQL. – Don Kirkby Mar 11 '13 at 23:35

2 Answers2