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 Ψ
– devnull Ψ

2022-02-13 15:48:26 +00:00
Commented Feb 13, 2022 at 15:48 — devnull Ψ
– devnull Ψ, Commented Feb 13, 2022 at 15:48

Lukasz Szozda · Accepted Answer · 2018-09-01 19:14:20Z

3

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;

answered Sep 1, 2018 at 19:14

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL auto increment column by scope of another column value

Example:items table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example:items table

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related