PostgreSQL auto increment column by scope of another column value

Question

In PostgreSQL we can define a column as SERIAL to auto increment it with insert query.

Can this auto increment happen by scoping to another column value ?

I will explain my question with below example.

In a multi tenant SaaS application, there could be a scenario where we may need to increment a column (say item_id column in items table) per user account.

Example:items table

item_id | account_id

100 | 1

101 | 1

102 | 1

100 | 2

sorry for the bad formatting of the table. I hope you got idea about the basic structure.

In the above example, as soon as a new item is created by a new account (say account 2), the item_id serial count should start from the beginning again i.e 100.

If user in account 1 deletes item_id 102 and creates a new item record, then item_id should set to 103 and so on.

I tried to find a solution but I could not get a pointer using Google search. So any help would be highly appreciated!

PS: I know this can be handled from backend code but I am more curious to know if database has a solution for this or not.

Did you find any appropriate solution? I have similar issue – devnull Ψ Feb 13 '22 at 15:48 — devnull Ψ, Feb 13 '22 at 15:48

score 3 · Answer 1 · answered Sep 01 '18 at 19:14

You can't define SERIAL to be partitioned over other column. But you could use ROW_NUMBER() function. If you don't need that value materialized you could write something like:

CREATE VIEW v_item_table
AS
SELECT *, 
 ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY some_timestamp_col) AS account_id
FROM item_table;

PostgreSQL auto increment column by scope of another column value

Example:items table

1 Answers1