INSERT multiple rows
One INSERT can add many rows at once, which is faster than many separate statements.
In this page:
Syntax
INSERT INTO table_name (column1, column2)
VALUES (value1, value2),
(value3, value4);INSERT multiple rows
Provide several parenthesised value lists separated by commas. This is a single statement, so it either inserts all rows or none.
For very large loads use COPY, which is much faster than INSERT.
PostgreSQL also supports INSERT ... SELECT to copy from another table.
Note:
COPY is the fastest way to bulk load data in PostgreSQL.
Example: INSERT multiple rows
CREATE TABLE fruits (id INTEGER PRIMARY KEY, name TEXT, color TEXT);
INSERT INTO fruits (id, name, color) VALUES (1, 'apple', 'red'), (2, 'banana', 'yellow'), (3, 'grape', 'purple');
SELECT COUNT(*) AS total FROM fruits;
CREATE TABLE red_fruits AS SELECT * FROM fruits WHERE color = 'red';
SELECT name FROM red_fruits;
-- Output:
-- total
-- 3
-- name
-- appleLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Running thousands of single-row inserts
- Mismatched value counts in one row
- Not wrapping large loads in a transaction
Chapter Summary
- List multiple VALUES tuples
- A single statement is all or nothing
- COPY loads bulk data fastest
- INSERT ... SELECT copies rows
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: