7

I have a table with columns taking default values:

create table indexing_table
(
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
);

How do I insert multiple default rows into this table? Do I have to repeat the command:

insert into indexing_table default values;

as many times as I want it to be inserted?

Alex
  • 15,186
  • 15
  • 73
  • 127
  • 1
    see https://dba.stackexchange.com/questions/89538/is-there-a-way-to-insert-multiple-rows-into-a-table-with-default-values-for-all – FuzzyTree Jan 29 '15 at 00:57
  • thanks, so something like `insert into indexing_table default VALUES from generate_series(1, 10);`? – Alex Jan 29 '15 at 01:06

4 Answers4

3

When you have a default value, you can tell the database to use this default value:

INSERT INTO indexing_table(id, created_at) 
VALUES(default, default), 
  (default, default), 
  (default, default);

If you need hundreds of default records and one of your defaults is "now()", use generate_series():

INSERT INTO indexing_table(created_at)
SELECT NOW() FROM generate_series(1,100);
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
3

I have same problem. Have header table which contains only id generated by sequence. I need insert rows into snapshot table and of course i must first fill header. So i have temporary table with many rows and want insert fast to both table.

Loop is easy to write, but its not a way to optimize query (In actualization process it run several thousand times on diferent databases/schemes)

Could run insert with specific values

INSERT INTO csh33 (id)
    SELECT (SELECT last_value FROM csh33_id_seq) + row_number() OVER ()
        FROM temp_tss11;

-- Primary key "id" is Serial so dont name it
INSERT INTO css33 (header_id, time_from, time_to, code, name)
    SELECT (SELECT last_value FROM csh33_id_seq) + row_number() OVER (), now(), null, code, name, FROM temp_tss11;

SELECT setval('csh33_id_seq', (SELECT max(id) FROM csh33) + 1);

Or i could don't name fields with default values

INSERT INTO csh33 SELECT FROM temp_tss11;

-- But must consider raised sequence in filling snapshot table (Don't care about ordering, so only subtract)
INSERT INTO css33 (header_id, time_from, time_to, code, name)
    SELECT (SELECT last_value FROM csh33_id_seq) - row_number() OVER (), now(), null, code, name, FROM temp_tss11;

But for you question

INSERT INTO yourTableName SELECT generate_series(1,100)

Note i use PG 9.4

Perlos
  • 2,028
  • 6
  • 27
  • 37
1

An option if you do not want to name any column keeping the default values syntax is to build it dynamically:

do $$
begin
execute (
    select string_agg('insert into indexing_table default values',';')
    from generate_series(1,10)
);
end; $$;

do

execute

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
-1

The simplist way is to insert now() into the "created_at" column like so:

insert into indexing_table (created_at)
select now()
;
Walker Farrow
  • 3,579
  • 7
  • 29
  • 51
  • did you mean to add `from generate_series(1,10)`? – Alex Jan 29 '15 at 01:20
  • You could, but if you just want to insert one record that would be the command. Or you could create a for-while loop and just re-fire the above sql statement. As a side note, this table seems pretty useless... What are you using it for? – Walker Farrow Jan 29 '15 at 01:27
  • it's just an example, this table might contain other fields which can be `NULL`. It is more important to be able to initialise the table to contain certain values for `id`. I also don't want to insert one record. If I did I believe `insert into indexing_table default values;` to be a better way. – Alex Jan 29 '15 at 02:34
  • No need for the `select`. An `insert into indexing_table values (current_timestamp)` will do just fine. But that was not the question actually. –  Jan 29 '15 at 06:55