PostgreSQL INSERT Multiple Rows

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

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);
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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)
);
Code language: SQL (Structured Query Language) (sql)

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');
Code language: SQL (Structured Query Language) (sql)

PostgreSQL returns the following message:

INSERT 0 3
Code language: Shell Session (shell)

To very the inserts, you use the following statement:

SELECT * FROM links;
Code language: SQL (Structured Query Language) (sql)

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 *;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Summary

• Specify multiple value lists in the INSERT statement to insert multiple rows into a table.
• Use RETURNING clause to return the inserted rows.