PostgreSQL Tutorial: INSERT Multiple Rows

August 1, 2023

Summary: in this tutorial, you will learn how to use a single PostgreSQL INSERT statement to insert multiple rows into a table.

Table of Contents

Introduction to insert multiple rows

To insert multiple rows into a table using a single INSERT statement, you use the following syntax:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

In this syntax:

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.

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;

Setting up a sample table

The following statement creates a new table called links:

DROP TABLE IF EXISTS links;

CREATE TABLE links (
    id SERIAL PRIMARY KEY,
    url VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255)
);

Inserting multiple rows example

The following statement uses the INSERT statement to add three rows to the links table:

INSERT INTO 
    links (url, name)
VALUES
    ('https://www.google.com','Google'),
    ('https://www.yahoo.com','Yahoo'),
    ('https://www.bing.com','Bing');

PostgreSQL returns the following message:

INSERT 0 3

To verify the inserts, you use the following statement:

SELECT * FROM links;

Output:

Inserting multiple rows and returning inserted rows

The following statement uses the INSERT statement to insert two rows into the links table and returns the inserted rows:

INSERT INTO 
    links(url,name, description)
VALUES
    ('https://duckduckgo.com/','DuckDuckGo','Privacy & Simplified Search Engine'),
    ('https://swisscows.com/','Swisscows','Privacy safe WEB-search')
RETURNING *;

If you just want to return the inserted id list, you can specify the id column in the RETURNING clause like this:

INSERT INTO 
    links(url,name, description)
VALUES
    ('https://www.searchencrypt.com/','SearchEncrypt','Search Encrypt'),
    ('https://www.startpage.com/','Startpage','The world''s most private search engine')
RETURNING id;