POSTGRES generate n rows based on a value n

Question

I need to generate a view, in postgres, containing n rows based on a value n in a column.

Let's make an example. I have a table like this:

   A*  |  B   |  C
 --------------------
  abc  |  def |  4
  ghi  |  jkl |  7

I need to generate a view made like this:

   A  |  B   |  C
------------------------
  abc  |  def |  4
  abc  |  def |  4
  abc  |  def |  4
  abc  |  def |  4
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7
  ghi  |  jkl |  7

Is there a way to do it smartly? At the moment I am doing N UNION ALL with N big enough to cover all the cases (e.g. in this case 7).

Gordon Linoff · Accepted Answer · 2018-04-11 15:24:38Z

6

Just use generate_series():

select t.*, generate_series(1, t.c)
from t ;

If you don't want the value in the result set, use a lateral join:

select t.*
from t, lateral
     generate_series(1, t.c);

Or:

select t.*
from t cross join lateral
     generate_series(1, t.c);

answered Apr 11, 2018 at 15:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

D-Shih · Accepted Answer · 2018-04-11 15:24:13Z

2

Using Recursive CTE

You can try this.

WITH RECURSIVE result(A,B,L,C) AS(
    SELECT A,B,1 L,MAX(C) C
   FROM T
    GROUP BY A,B
    UNION ALL
    SELECT A,B,L+1,C
    FROM result
    WHERE L+1 <= C
)
SELECT A,B,C
FROM result
ORDER BY C,A

SQLFIDDLE

answered Apr 11, 2018 at 15:24

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

2 Comments

balsick Over a year ago

thank you for your answer, I'm accepting the other one because it's much more compact

D-Shih Over a year ago

You are welcome,Gordon‘s solution is better than my :)

Collectives™ on Stack Overflow

POSTGRES generate n rows based on a value n

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related