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?

You can try to use an updatable VIEW for this table that has row_number() OVER (PARTITION BY bar_id ORDER BY id) as friendly_id — Ihor Romanchenko
– Ihor Romanchenko, Commented Feb 21, 2013 at 21:42

Clodoaldo Neto · Accepted Answer · 2013-02-22 00:10:44Z

6

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

answered Feb 22, 2013 at 0:10

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Over a year ago

Thanks! I was hoping I was just missing a built-in way to accomplish this using sequences, but this works just as well.

Collectives™ on Stack Overflow

Incrementing a sequence in PostgreSQL based on a foreign key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related