In PostgreSQL, the INSERT INTO command inserts one or multiple rows into a Postgres table. Multi-row insertion requires the use of comma-separated syntax. PostgreSQL provides a RETURNING clause that can be used with the INSERT query to return the currently inserted rows.

This write-up will teach you how to insert multiple rows in a table with the help of examples. So, let’s start!

How to Insert Multiple Rows to a Table in PostgreSQL?

Specify the comma-separated values in the INSERT query to add/insert multiple rows in a specific table. Use the following syntax for inserting multiple rows to a table in Postgres:

INSERT INTO tab_name (col_list)
VALUES
(val_list_1),
(val_list_2),
...
(val_list_n);

Let’s understand what the above syntax says:

● Use the INSERT INTO query followed by the table name, into which you want to insert the rows.

● Next, specify a column or list of columns in the parentheses.

● Finally, specify a VALUES keyword followed by the list of values to be inserted in multiple rows.

Example #1: How to Insert Multiple Rows to a Table in PostgreSQL?

We have already created a table named article_details whose details are as follows:

SELECT * FROM article_details;

The output shows that the article_details table has three columns: article_id, article_title, and published_date. Let’s run the INSERT INTO statement to insert five new rows to the selected table:

INSERT INTO 
article_details(article_id, article_title, published_date)
VALUES
  ('5', 'PostgreSQL WHERE Clause', '2022-07-01'),
  ('2', 'PostgreSQL LIKE Operator','2022-07-15'),
  ('1', 'PostgreSQL CREATE TABLE','2022-07-15'),
  ('7', 'PostgreSQL DROP TABLE','2022-07-18'),
  ('4', 'PostgreSQL TRUNCATE TABLE','2022-08-05');

You can check/verify the inserted data by executing the below-given query:

SElECT * FROM article_details;

The output clarifies that five rows have been inserted into the targeted table successfully.

Example #2: How to Insert and Return Multiple Rows in PostgreSQL?

Postgres provides an optional clause named RETURNING that can be used with the INSERT statement to return the currently inserted data:

INSERT INTO 
article_details(article_id, article_title, published_date)
VALUES
  ('3', 'PostgreSQL INSERT Query', '2022-07-01'),
  ('6', 'PostgreSQL DELETE Query','2022-07-15')
  RETURNING *;

The output shows that the RETURNING clause returns the newly inserted rows. Let’s execute the below command to see the updated table:

SELECT * FROM article_details;

PostgreSQL maintains the insertion order. However, the ORDER BY clause can be used to sort the table’s data in a specific order. Let’s run the following command to sort the rows in ascending order:

SELECT * FROM article_details
ORDER BY article_id ASC;

The output verified that the ORDER BY clause sorted the rows in ascending order (based on article_id).

Conclusion

To insert multiple rows in a table, the comma-separated syntax is used. To do that, use the INSERT INTO query followed by the table name into which you want to insert the rows. Next, specify a column or list of columns in the parentheses. Finally, specify a VALUES keyword followed by the list of values to be inserted in multiple rows. PostgreSQL provides a returning clause that can be used with the INSERT query that returns the currently inserted rows. This write-up shows how to insert multiple rows into a Postgres table with the help of several examples.