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?
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);
(default, default), 100 times?
generate_series() with insert into table DEFAULT VALUES?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
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; $$;
The simplist way is to insert now() into the "created_at" column like so:
insert into indexing_table (created_at)
select now()
;
from generate_series(1,10)?
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.
insert into indexing_table default VALUES from generate_series(1, 10);?