Select query to Increment an integer revision counter by 1 or setting it to 0 for first revision

Question

I have a versioned object that is stored into a relational database. The entries are identified by an Integer ID and a revision number. On storing a new revision I want to automatically increment the number

-- Describe FOO
CREATE TABLE foo (
    id INTEGER NOT NULL, 
    revision INTEGER NOT NULL, 
    content TEXT,
    PRIMARY KEY (id, revision)
)

I want to use a select query to automatically give the highest revision number + 1 if a revision already exists or 0 if no result could be found. The first part is easy, e.g. if I have the following data:

id | revision | content
1  | 0        | foo
1  | 1        | bar
1  | 2        | bam
1  | 3        | bang

then

 select max(revision) + 1 from foo where foo.id = 1
 > 3

Are there simple tweaks I can apply to the query to make it return 0 when I'm querying to foo.id = 2?

count(*) would be an option, but even though we're using a write-only-once scheme, I don't want to rely on all revisions from 0-current_revision being present in the DB.

Answer 1

You are looking for the coalesce() function:

select coalesce(max(revision) + 1, 0)
from foo
where foo.id = 1;