How do I get the MIN() of two fields in Postgres?

Question

Let's say I have a table like this:

name | score_a | score_b

-----+---------+--------

 Joe |   100   |   24

 Sam |    96   |  438

 Bob |    76   |  101

 ... |   ...   |  ...

I'd like to select the minimum of score_a and score_b. In other words, something like:

SELECT name, MIN(score_a, score_b)

FROM table

The results, of course, would be:

name | min

-----+-----

 Joe |  24

 Sam |  96

 Bob |  76

 ... | ...

However, when I try this in Postgres, I get, "No function matches the given name and argument types. You may need to add explicit type casts." MAX() and MIN() appear to work across rows rather than columns.

Is it possible to do what I'm attempting?

Answer 1

Try this:

LEAST(a, b):

For more information, you can refer to https://www.postgresql.org/docs/current/functions-conditional.html#AEN15582

The GREATEST and the LEAST functions are used to select the largest or smallest value from a list of any number of expressions. The expressions should be convertible to a common data type, which will be the type of the result. Whereas, the NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Note: GREATEST and LEAST are not in the SQL standard, but they can be considered as a common extension. But, some other databases make them return NULL if any argument is NULL, rather than only when all are NULL.