sqlpostgresqlset-returning-functions

Repeat rows specified number of times in PostgreSQL


My PostgreSQL table:

create table your_table(name,cc)as values
 ('a',  2)
,('b',  3)
,('c',  4)
,('d',  3);

I need to get a result like this:

name cc
a 2
a 2
b 3
b 3
b 3
c 4
c 4
c 4
c 4
d 3
d 3
d 3

It's the rows with name and cc, each repeated cc times.

How can I do it in SQL? Should I use python instead?


Solution

  • You can cross join with a set-returning function generate_series(). All it does is spawn rows, and you can ask it to generate cc amount of them for each row of your table.
    demo at db<>fiddle

    select name,cc 
    from your_table 
    cross join lateral generate_series(1,greatest(cc,1));
    

    LATERAL is there to underline that the second FROM object references data from the first one, which normally isn't possible.

    The greatest(cc,1) might be handy in case your cc can be null, 0 or negative, if you always want at least a single output row for each input row:

    name cc
    a 2
    a 2
    b 3
    b 3
    b 3
    c 4
    c 4
    c 4
    c 4
    d 3
    d 3
    d 3
    e null
    f 0
    g -1

    In practice, SRF calls act like lateral is always implied, so it's optional - and so is the cross join:

    select name,cc 
    from your_table, generate_series(1,cc);
    

    But the comma makes it a widely disliked old-style join syntax, so the explicit variant is preferred.