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

Postgresql

Postgresql Problem Overview

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);

Postgresql Solutions

Solution 1 - Postgresql

Have a look at GREATEST and LEAST.

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

Solution 2 - Postgresql

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.

Categories
Recommended Postgresql Solutions

Previous Solution:

Reverting single file in SVN to a particular revision

SvnRevert

Next Solution:

jQuery table sort

JquerySortingHtml Table

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionHRJView Question on Stackoverflow
Solution 1 - PostgresqlMark ByersView Answer on Stackoverflow
Solution 2 - PostgresqlDonnieView Answer on Stackoverflow