PostgreSQL – Creating Unique Sequence for Rows with Same Foreign Key

database-designpostgresqlpostgresql-9.3sequence

In my system, I want each user to have an own id sequence for his articles.

So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the outline of such table:

    +------+----------+---------+
    | id   | article  | user_id |
    +------+----------+---------+
    | 1    |some notes| 1       |
    +------+----------+---------+
    | 2    |some notes| 1       |
    +------+----------+---------+
    | 3    |some notes| 2       |
    +------+----------+---------+ 
    | 4    |some notes| 2       |
    +------+----------+---------+
    | 5    |some notes| 3       |
    +------+----------+---------+

But I want to generate the ids in this table in the following way:

    +------+----------+---------+
    | id   | article  | user_id |
    +------+----------+---------+
    | 1    |some notes| 1       |
    +------+----------+---------+
    | 2    |some notes| 1       |
    +------+----------+---------+
    | 1    |some notes| 2       | 
    +------+----------+---------+
    | 2    |some notes| 2       |
    +------+----------+---------+
    | 3    |some notes| 2       |
    +------+----------+---------+
    | 1    |some notes| 3       |
    +------+----------+---------+

So, when a new user is being created in the associated user table, an id sequence for his articles will start from 1.

Is there any generally accepted approach to solve this problem?

Best Answer

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column and create a VIEW to display a running number per user_id.

CREATE VIEW v_user_article AS
SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY id) AS per_user_id
FROM   user_article;

A column article with 'some notes' hardly makes sense for your setup. And ordering by it makes even less sense, since the numbers would change with every new article that does not happen to sort last. To get a stable sort order and stable numbers (except for deletes!) use the serial column to determine the sort order.

Details:

Related Question