Is there a way to add the same row multiple times with different ids into a table with postgresql?

user_16651657 0
Viewed 99

I am trying to add the same data for a row into my table x number of times in postgresql. Is there a way of doing that without manually entering the same values x number of times? I am looking for the equivalent of the go[count] in sql for postgres...if that exists.

postgresql
2 Answers

Use the function generate_series(), e.g.:

insert into my_table
select id, 'alfa', 'beta'
from generate_series(1,4) as id;

Test it in db<>fiddle.

user_19957380

Idea

Produce a resultset of a given size and cross join it with the record that you want to insert x times. What would still be missing is the generation of proper PK values. A specific suggestion would require more details on the data model.

Query

The sample query below presupposes that your PK values are autogenerated.

CREATE TABLE test ( id SERIAL, a VARCHAR(10), b VARCHAR(10) );

INSERT INTO test (a, b)
    WITH RECURSIVE Numbers(i) AS (
           SELECT 1
        UNION ALL
           SELECT i + 1
             FROM Numbers
            WHERE i < 5 -- This is the value `x`
    )
         SELECT adhoc.*
           FROM Numbers n
     CROSS JOIN ( -- This is the single record to be inserted multiple times
                   SELECT 'value_a' a
                        , 'value_b' b
                ) adhoc
 ;

See it in action in this db fiddle.

Note / Reference

The solution is adopted from here with minor modifications (there are a host of other solutions to generate x consecutive numbers with SQL hierachical / recursive queries, so the choice of reference is somewhat arbitrary).

user_5889160
Related