How to insert multiple default rows in a table in PostgresQL

Question

I have a table with columns accepting default values:

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

How to insert multiple default rows in this table? Should I repeat the command:

 insert into indexing_table default values; 

as many times as I want it to be inserted?

Answer 1

I have the same problem. It has a header table that contains only the identifier generated by the sequence. I need to insert rows into the snapshot table, and of course I have to fill in the title first. So I have a temporary table with many rows and want to quickly insert into both tables.

Loop is easy to write, but this is not a way to optimize the query (during the update process, it starts several thousand times on different databases / schemas)

May trigger insertion 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 can’t name the 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 the question is for you

 INSERT INTO yourTableName SELECT generate_series(1,100) 

Note: I am using PG 9.4

Answer 2

If 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 entries and one of your default values ​​is "now ()", use the generate_series () function:

 INSERT INTO indexing_table(created_at) SELECT NOW() FROM generate_series(1,100); 

Answer 3

The parameter, if you do not want to specify any column that supports the default values syntax, is to dynamically build it:

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

do

execute

Answer 4

The easiest way is to insert now () in the created_at column as follows:

 insert into indexing_table (created_at) select now() ;