CODEWITHSUNDEEP
sqlpostgresqlselect
jeffda
jeffda

Reputation:

PostgreSQL: Select a single-row x amount of times

A single row in a table has a column with an integer value >= 1 and must be selected however many times the column says. So if the column had '2', I'd like the select query to return the single-row 2 times.

How can this be accomplished?

Upvotes: 5

Views: 7644

Answers (4)

Cleiton Soares
Cleiton Soares

Reputation: 11

The simplest way is making a simple select, like this:

SELECT generate_series(1,{xTimes}), a.field1, a.field2 FROM my_table a;

Upvotes: 1

rfusca
rfusca

Reputation: 7705

Don't know why you would want to do such a thing, 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

Upvotes: 19

Evan
Evan

Reputation: 18599

You could make a table that is just full of numbers, like this:

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

and populate it with as many numbers as you need, starting from one:

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

Then, if you had the table "mydata" that han to repeat based on the column "repeat_count" you would query it like so:

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

If course you need to know the maximum repeat count up front, and have your numbers table go that high.

No idea why you would want to do this thought. Care to share?

Upvotes: 4

Charlie
Charlie

Reputation: 644

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

something like

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;

Upvotes: 1

Related Questions