CODEWITHSUNDEEP
sqlpostgresqlpostgresql-11
Rob
Rob

Reputation: 791

Is there a way to repeat rows based on a counter column in Postgres?

I have two tables A and B.

A has an id for rows in table B, and a counter:

b_id  integer not null references B(id),
count integer not null default 1

Is there a way in Postgres to return query of A with the rows in B where the rows are repeated count times?

Upvotes: 1

Views: 692

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271181

Yes. Use generate_series():

select t.*, n
from t cross join lateral
     generate_series(1, t.count, 1) gs(n);

The above is actually the verbose way of writing the logic. I prefer the above, because it is quite explicit about what is happening. However, you can simplify this to:

select t.*, generate_series(1, t.count, 1) as n
from t;

Upvotes: 2

Related Questions