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:

img

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 *;

img

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;

img

Summary

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

Search

Categories

Tags