PostgreSQL: select a single-line number x times

Question

There is a column in one row of the table with an integer value> = 1 and must be selected, however, many times, as the column says. Therefore, if the column has "2", I would like the select query to return a single row 2 times.

How can I do that?

Answer 1

I don’t know why you would like to do this, but ...

CREATE TABLE testy (a int,b text);
INSERT INTO testy VALUES (3,'test');
SELECT testy.*,generate_series(1,a) from testy;  --returns 3 rows

Answer 2

You can make a table that is simply filled with numbers, for example:

CREATE TABLE numbers
(
  num INT NOT NULL
, CONSTRAINT numbers_pk PRIMARY KEY (num)
);

and fill it with the number you need, starting with one:

INSERT INTO numbers VALUES(1);
INSERT INTO numbers VALUES(2);
INSERT INTO numbers VALUES(3);
...

Then, if you had a table "mydata" that repeats based on the column "repeat_count", you will query it like this:

SELECT mydata.*
FROM mydata
JOIN numbers
ON numbers.num <= mydata.repeat_count
WHERE ...

, .

, . ?

Answer 3

You can do this with a recursive query, check the examples in postgresql docs .

sort of

WITH RECURSIVE t(cnt, id, field2, field3) AS (
        SELECT 1, id, field2, field3
        FROM foo
      UNION ALL
        SELECT t.cnt+1, t.id, t.field2, t.field3
        FROM t, foo f
        WHERE t.id = f.id and t.cnt < f.repeat_cnt
)
SELECT id, field2, field3 FROM t;