How do I insert a record into a Postgres DB based off the MAX of one column?

Question

I have a table with a name field and a number field (neither is unique). Like so:

| name    | number |
|---------+--------|
| Bob     |      2 |
| Bob     |      8 |
| Charlie |      4 |

I want to run a query that meets the following specifications: I'm given a name. If that name DOES NOT exist in the table, insert a new record with the name and the number 1. If the name DOES exist in the table, insert a new record that is 1 greater than the highest number associated with the given name.

For example, with the above table, if I'm told to insert "Bob," I would do this:

 | name    | number |
 |---------+--------|
 | Bob     |      2 |
 | Bob     |      8 |
>| Bob     |      9 |
 | Charlie |      4 |

If I'm then told to insert "Steve," I would do this:

 | name    | number |
 |---------+--------|
 | Bob     |      2 |
 | Bob     |      8 |
 | Bob     |      9 |
 | Charlie |      4 |
>| Steve   |      1 |

This is for a web service so it needs to consider the danger of a race condition.

I've tried using CASE statements, like the following, but I can't seem to figure out how to extract the MAX.

SELECT name,
        CASE
                WHEN (SELECT COUNT(*) FROM t WHERE name = 'Bob') != 0 THEN (SELECT MAX(number) FROM t WHERE name = 'Bob') + 1 ELSE 1
        END
FROM t;

Answer 1

You do not need the case expression. You can just go straight after max(number) for the given name. If you do so and the name does not exist the max() returns returns NULL; so use the coalesce() function to handle a null returned.

insert into <table>(name, number)
        select '<name>',  1+coalesce( max(t.number),0) 
          from <table> t 
         where name = '<name>';

Further, if needed often, the above converts easily to a function or procedure. (See demo here.)