Postgres: Mathematical functions in COALESCE

I have a table which contains ID column:

ID
---
1
2
3
4
5

I have to retrieve an ID using a simple query. If the ID is not present in the table, then I want to display ID – 1

For example, In the above table, there is no ID = 7. So if I query something like:

SET @id = 7

SELECT COALESCE(id, @id - 1)
FROM table
WHERE id = @id

I understand it gives me NULL because the condition WHERE id = 7 do not match.

Here are the conditions I want to cover:

1. If ID is present in the table, return ID
2. If ID is not present in the table, return ID – 1

Is there any other approach I can use to get my desired output?

Answer

Using MAX() will let you either keep the id found in the table and otherwise return the computed value. If you didn’t use an aggregate function then there wouldn’t be any table rows to return when the id doesn’t match anything.

SET @id = 7
SELECT COALESCE(MAX(id), @id - 1)
FROM table
WHERE id = @id