Returns the absolute value of a number — its value without the sign.
ABS(<number>)
A negative number becomes positive, while positive numbers and zero stay as they are: ABS(-7) gives 7. Most often it's used to measure a distance or difference when the direction doesn't matter: how far a value is from a target, how far a forecast is from reality.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| negative | positive | fraction |
|---|---|---|
| 7 | 7 | 2.5 |
| name | salary | distance_from_1300 |
|---|---|---|
| Emma | 1200 | 100 |
| Liam | 1000 | 300 |
| Olivia | 1350 | 50 |
| Noah | 950 | 350 |
| Sophia | 1200 | 100 |
| name | salary |
|---|---|
| Olivia | 1350 |
When sorting by a difference, remember equal distances: any of them could end up “closest”. Add a second field to ORDER BY.
The absolute value removes the sign, so you can no longer tell whether the value is bigger or smaller. If the direction matters, look at the difference itself or at SIGN.
The smallest number of a type has no positive counterpart: ABS((-2147483648)::integer) throws integer out of range.