Menu
As described in the PostgreSQL manual one can add multiple rows in a single INSERT statement:
INSERT INTO films (code, title, did, date_prod, kind) VALUES
('6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('6120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
Also a nested SELECT query like this works:
INSERT INTO films (code, title, did, date_prod, kind)
SELECT table.code, 'Tampopo', 110, '1985-02-10', 'Comedy'
FROM other_table AS table2;
However, I can't seem to figure out a way to do both at the same time:
INSERT INTO films (code, title, did, date_prod, kind)
SELECT
(table.code + 100, 'abc', NULL, t2.date_prod, t2.kind),
(table.code + 200, 'xyz', NULL, t2.date_prod, t2.kind)
FROM other_table AS t2;
If other_table contained only (61717 | 'Tampopo' | NULL | '1985-02-10' | 'Comedy'), the result table would look like this:
code | title | did | date_prod | kind
--------------------------------------------------
61817 | 'abc' | NULL | '1985-02-10' | 'Comedy'
61917 | 'xyz' | NULL | '1985-02-10' | 'Comedy'
This fiddle should explain it further...
Could you please point me to where I am going wrong. If possible, I would like to avoid multiple lookups in other_table AS t2 because it is a VIEW and it takes considerable time to construct.
778
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
You can insert a maximum of 1000 rows in a single statement in PostgreSQL. If you want to insert more than 1000 records, you need to run INSERT INTO statement multiple times. PostgreSQL INSERT Multiple Rows ON CONFLICT ON CONFLICT are the keywords used to apply the UPSERT feature in PostgreSQL.
PostgreSQL INSERT Multiple Rows RETURNING You can also return the inserted rows information in PostgreSQL by using the RETURNING clause in the INSERT INTO statement. The syntax is as follows: INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2),......... (value_list_n) RETURNING output_expression [AS output_name];
When you insert a new row into the table, PostgreSQL will UPDATE the row if it already exists, otherwise, the new row will be inserted. target can be any column, a UNIQUE constraint, or a WHERE predicate statement.
Third, supply a comma-separated list of rows after the VALUES keyword. To insert multiple rows and return the inserted rows, you add the RETURNING clause as follows: INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), ... (value_list_n) RETURNING * | output_expression;
Generally you can use select with union:
insert into films (code, title, did, date_prod, kind)
select t2.code + 100, t2.title, NULL::float, t2.date_prod, t2.kind
from other_table AS t2
union
select t2.code + 200, t2.title, NULL::float, t2.date_prod, t2.kind
from other_table AS t2;
In the particular case you described, you can use the unnest (array []):
insert into films (code, title, did, date_prod, kind)
select
unnest(array[t2.code + 100, t2.code + 200]),
t2.title, NULL::float, t2.date_prod, t2.kind
from other_table AS t2
You can use INSERT INTO ... UNION ALL:
INSERT INTO films (code, title, did, date_prod, kind)
SELECT t2.code + 100, t2.title, t2.did, t2.date_prod, t2.kind
FROM other_table AS t2
UNION ALL
SELECT t2.code + 200, t2.title, t2.did, t2.date_prod, t2.kind
FROM other_table AS t2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
