Select a row and insert it with different IDs n times

Question

I am trying to come up with a script in Postgres that will select the first row in a table and insert that row x number of times back into the same table.

Here is what I have:

INSERT INTO campaign (select column_name from campaign)
SELECT x.id from generate_series(50, 500) as x(id);

The above obviously doesn't work.

Answer 1

Just get the syntax for the INSERT statement right:

INSERT INTO campaign (id, column_name)
SELECT g.g, t.column_name
FROM  (SELECT column_name FROM campaign LIMIT 1) t  -- picking arbitrary row
      ,generate_series(50, 500) g(g);               -- 451 times

The CROSS JOIN to generate_series() multiplies each selected row.

Selecting one arbitrary row, since the question didn't define "first". There is no natural order in a table. To pick a certain row, add ORDER BY and/or WHERE.

There is no syntactical shortcut to select all columns except the one named "id". You have to use the complete row or provide a list of selected columns.

Automation with dynamic SQL

To get around this, build the query string from catalog tables (or the information schema) and use EXECUTE in a plpgsql function (or some other procedural language). Only using pg_attribute.
format() requires Postgres 9.1 or later.

CREATE OR REPLACE FUNCTION f_multiply_row(_tbl regclass
                                        , _idname text
                                        , _minid int
                                        , _maxid int)
  RETURNS void AS
$func$
BEGIN

   EXECUTE (
      SELECT format('INSERT INTO %1$s (%2$I, %3$s)
                     SELECT g.g, %3$s
                     FROM  (SELECT * FROM %1$s LIMIT 1) t
                           ,generate_series($1, $2) g(g)'
                   , _tbl
                   , _idname
                   , string_agg(quote_ident(attname), ', ')
                   )
      FROM   pg_attribute 
      WHERE  attrelid = _tbl
      AND    attname <> _idname  -- exclude id column
      AND    NOT attisdropped    -- no dropped (dead) columns
      AND    attnum > 0          -- no system columns
      )
   USING _minid, _maxid;

END
$func$ LANGUAGE plpgsql;

Call in your case:

SELECT f_multiply_row('campaign', 'id', 50, 500);

SQL Fiddle.

Major points

• Properly escape identifiers to avoid SQL injection. Using format() and regclass for the table name. Details:

  • Table name as a PostgreSQL function parameter

• _idname is the column name to exclude ('id' in your case). Case sensitive!

• Pass values in the USING clause. $1 and $2 in generate_series($1, $2) reference those parameters (not the function parameters).

More explanation in related answers. Try a search:
https://stackoverflow.com/search?q=[plpgsql]+[dynamic-sql]+format+pg_attribute