Incrementing a sequence in PostgreSQL based on a foreign key

Question

I would like to use sequences to create friendly IDs for some objects in my database. My problem is that I don't want the sequence to be global to the table, instead I want to increment the value based on a foreign key.

For example, my table is defined as:

CREATE TABLE foo (id numeric PRIMARY KEY, friendly_id SERIAL, bar_id numeric NOT NULL)

And I would like friendly_id to increment separately for each bar_id such that the following statements:

INSERT INTO foo (123, DEFAULT, 345)
INSERT INTO foo (124, DEFAULT, 345)
INSERT INTO foo (125, DEFAULT, 346)
INSERT INTO foo (126, DEFAULT, 345)

Would result in (desired behavior):

id         | friendly_id      | bar_id
-----------+------------------+-----------------
123        | 1                | 345
124        | 2                | 345
125        | 1                | 346
126        | 3                | 345

Instead of (current behavior):

id         | friendly_id      | bar_id
-----------+------------------+-----------------
123        | 1                | 345
124        | 2                | 345
125        | 3                | 346
126        | 4                | 345

Is this possible using sequences or is there a better way to achieve this?

Answer 1

create table foo (
    id serial primary key,
    friendly_id integer not null,
    bar_id integer not null,
    unique(friendly_id, bar_id)
);

At the application wrap the insertion in a exception catching loop to retry if a duplicate key exception is raised

insert into foo (friendly_id, bar_id)
select
    coalesce(max(friendly_id), 0) + 1,
    346
from foo
where bar_id = 346