Question 114242 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create n rows up to highest value in column for each unique client

I have this data in my postgresSQL db.

id client_id num
1 1 3
2 2 1
3 3 2

I want to create multiple entries for each unique client corresponding to the number in the num column. For example to complete this task for the above column I would have.

id client_id num
1 1 3
2 2 1
3 3 2
4 1 1
5 1 2
6 3 1

Filling in all numbers between 0 and num with new entries. I’d prefer to do this in a single command or loop versus the manually adding entries method that I know. Any help would be appreciated, thanks.

Tags:

2

Answers


  1. 0

    You can do it with a RECURSIVE query, that decrements your num index by one at each iteration, and stop when this value is equal to 1.

    WITH RECURSIVE cte AS (
    SELECT id, client_id, num FROM tab
    UNION ALL
    SELECT id, client_id, num-1 
    FROM cte
    WHERE num-1 > 0
)
SELECT * FROM cte ORDER BY id, client_id, num

    Check the demo here.

    Login or Signup to reply.
  2. 0

    You can do this by joining with generate_series using num as the max value. Use row_number to generate an ID for each row.

    select
  row_number() over() as id,
  client_id,
  g.num as num
from clients
cross join generate_series(1, clients.num) as g(num)

    Demonstration

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search