PostgreSQL INSERT Multiple Rows

In this PostgreSQL tutorial, we will discuss how to insert multiple rows in PostgreSQL in a single statement.

PostgreSQL INSERT Multiple Rows

You can insert more than one row at a time in a single statement in PostgreSQL by specifying comma-separated multiple-row values in value list form as VALUES in the INSERT INTO statement.

Syntax

To insert multiple rows in PostgreSQL, follow the syntax below

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),

...
...
...
(value_list_n);

In the above syntax:

• table_name is specified after the keywords INSERT INTO, which is the name of the table to which you want to insert rows.
• column_list is the list of columns that must be specified, whose values you want to insert into the table within parentheses.
• Comma-separated multiple value lists have to be supplied after the keyword VALUES.
• The above statement will return a command tag in the form INSERT OID COUNT.
• NOTE – The columns and values in the column list and value list should be in the same order.

In the command tag,

• OID is an object identifier. PostgreSQL uses the OID as the primary key for the tables in the system. The INSERT statement returns zero as the OID value.
• The COUNT is the number of rows inserted successfully by the INSERT statement.

Example:

CREATE TABLE device_data (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50) UNIQUE,
gender VARCHAR(20),
ip_address VARCHAR(20) NOT NULL);

\d

\d device_data

After executing the above script, I obtained the expected output, as shown in the screenshot below.

INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
VALUES
('Bonnibelle', 'Keefe', 'bkeefe0@woothemes.com', 'Female', '150.100.165.158'),
('Davin', 'Sandercroft', 'dsandercroft0@nps.gov', 'Male', '37.70.16.47'),
('Maximo', 'Barks', 'mbarks1@com.com', 'Female', '136.28.227.120'),
('Didi', 'Diver', 'ddiver2@fastcompany.com', 'Male', '187.115.101.3'),
('Sully', 'Rodge', 'srodge3@seesaa.net', 'Female', '186.74.37.211'),
('Aguste', 'Mouser', 'amouser4@usatoday.com', 'Male', '57.27.254.99'),
('Adrianna', 'Bowler', 'abowler5@feedburner.com', 'Female', '43.124.224.115');

SELECT * FROM device_data;

Check out the screenshot below; we got the expected output.

Read PostgreSQL ALTER TABLE + 19 Examples

Example 1: Using the RETURNING Clause

You can also return the information for the inserted rows in PostgreSQL using the RETURNING clause in the INSERT INTO statement. The syntax is as follows:

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),

...
...
...
(value_list_n)
RETURNING output_expression [AS output_name];

In the above syntax,

• output_expression after the keyword RETURNING is the expression that specifies the columns to be returned as the output.
• If you put an asterix(*) as output_expression, it will return all the columns from the inserted rows as the output.
• You can also rename the returned value in the RETURNING clause by using the AS keyword followed by the name of the output, here output_name.

Example:

INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
VALUES
('Terrence', 'Mathen', 'tmathen6@bandcamp.com', 'Male', '123.172.105.139'),
('Bar', 'Raubenheim', 'braubenheim7@php.net', 'Female', '189.175.233.255'),
('Teena', 'Billam', 'tbillam8@list-manage.com', 'Male', '93.211.102.195'),
('Chester', 'Champagne', 'cchampagne9@prnewswire.com', 'Male', '103.95.239.132')
RETURNING first_name, email, ip_address;

INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
VALUES
('Kev', 'Stanistreet', 'kstanistreeta@howstuffworks.com', 'Female', '145.182.171.234'),
('Karina', 'Jochens', 'kjochensb@cnet.com', 'Male', '105.165.36.142'),
('Leeland', 'Carwithen', 'lcarwithenc@ed.gov', 'Female', '188.125.13.65')
RETURNING *;

SELECT * FROM device_data;

After executing the above script, I got the expected output as shown in the screenshot below.

Read PostgreSQL DATE Functions with Examples

Example 2: Using ON CONFLICT

ON CONFLICT are the keywords used to apply the UPSERT feature in PostgreSQL. The syntax is as follows:

INSERT INTO table_name(column_list) 
VALUES
(value_list_1),
(value_list_2),
...
...
...
(value_list_n)
ON CONFLICT target
action;

In the above syntax,

• Let’s get to know about the UPSERT features:
  • In relational databases, UPSERT is referred to as merge, UP—update, and SERT—insert. When you insert a new row into the table, PostgreSQL will UPDATE the row if it already exists; otherwise, the new row will be inserted.

• target can be any column, a UNIQUE constraint, or a WHERE predicate statement. It specifies where it will check for the uniqueness of the rows inserted with the existing rows.

(column_name)     -- A column name where unique constraint is specified


-- Here, constraint_name can be the name of the UNIQUE constraint

ON CONSTRAINT constraint_name


WHERE predicate   -- It is a WHERE clause with a predicate

• If the inserted row already exists, it will be updated according to the action. The action can be any UPDATE statement or DO NOTHING keywords. The UPDATE statement will update the existing row, and the later one will do nothing; that is, the row will neither be inserted nor updated.

DO NOTHING       -- It means do nothing, if the row already exists


-- The following statement means update some fields in the table

DO UPDATE SET column1 = value1, column2 = value2, ...
WHERE condition

Example:

\d device_data

INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
VALUES
('Terrence', 'Mathen', 'tmathen6@bandcamp.com', 'Male', '123.172.105.139'),
('Carola', 'Mc Caghan', 'cmccaghand@yale.edu', 'Female', '129.74.4.91'),
('Chrystel', 'Cavolini', 'ccavolinie@tripadvisor.com', 'Female', '55.230.53.60'),
('Kev', 'Stanistreet', 'kstanistreeta@howstuffworks.com', 'Female', '145.182.171.234')
ON CONFLICT (email)
DO NOTHING
RETURNING *;

INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
VALUES
('Terrence', 'Mathen', 'iamathen6@revendo.com', 'Male', '123.172.105.139'),
('Rolland', 'Penke', 'rpenkef@yale.edu', 'Female', '175.49.210.144'),
('Vernor', 'Downse', 'vdownseg@alexa.com', 'Female', '207.227.255.171')
ON CONFLICT (email)
DO
UPDATE SET email = EXCLUDED.email || ';' || device_data.email
RETURNING *;

After executing the above query, I got the expected output as shown in the screenshot below.

Read PostgreSQL DROP COLUMN

Example 3: Using INSERT INTO

You can insert multiple rows in a table if it does not exist already in PostgreSQL by applying the UPSERT feature in the INSERT INTO statement by using the ON CONFLICT clause and using DO NOTHING as the action, as explained above. The syntax is as follows:

INSERT INTO table_name(column_list) 
VALUES
(value_list_1),
(value_list_2),
...
...
...
(value_list_n)
ON CONFLICT target
DO NOTHING;

The above syntax is explained in the previous topic. So, let’s do an example for practice.

Example:

\d device_data

INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
VALUES
('Vernor', 'Downse', 'vdownseg@alexa.com', 'Female', '207.227.255.171'),
('Rolland', 'Penke', 'rpenkef@yale.edu', 'Female', '175.49.210.144'),
('Merwin', 'Orcas', 'morcash@bigcartel.com', 'Female', '103.131.233.26')
ON CONFLICT ON CONSTRAINT device_data_email_key
DO NOTHING
RETURNING *;

There is one more way through which you can insert multiple rows in a table if they do not exist in PostgreSQL by using the NESTED SELECT query. The syntax is as follows:

WITH data(column_list) AS (
VALUES
(value_list_1),
(value_list_2),
...
...
...
(value_list_n)

)
INSERT INTO table_name (column_list) 
SELECT data.column_name1, data.column_name2, ...
FROM data
WHERE NOT EXISTS
(SELECT *
 FROM table_name
WHERE table_name.unique_column = data_object.unique_column);

In the above syntax,

• WITH keyword is used to create a temporary table with name data, where you populate the rows you want to insert in the table table_name.
• Then, an INSERT INTO statement is used to insert selected rows into the table_name from the temporary table data.
• Here, Nested SELECT statement is used to select the data not present in the table table_name.

Example:

WITH data (first_name, last_name, email, gender, ip_address) AS (
VALUES
('Merwin', 'Orcas', 'morcash@bigcartel.com', 'Female', '103.131.233.26'),
('Bran', 'Dight', 'bdighti@istockphoto.com', 'Male', '47.139.253.134'),
('Bartolomeo', 'Hughlin', 'bhughlinj@wordpress.org', 'Male', '131.241.74.251'),
('Chrystel', 'Cavolini', 'ccavolinie@tripadvisor.com', 'Female', '55.230.53.60')
)
INSERT INTO device_data (first_name, last_name, email, gender, ip_address)
SELECT data.first_name, data.last_name, data.email, data.gender, data.ip_address
FROM data
WHERE NOT EXISTS
( SELECT * FROM device_data
WHERE device_data.email = data.email)
RETURNING *;

Example 4: From an array

You cannot INSERT Multiple Rows from an array in PostgreSQL, because an array is a named list of the same data objects. It means, it can hold data of the same type (homogenous) while columns of a table can be heterogeneous of different types. So there is no way to insert a row from an array.

Read PostgreSQL INSERT INTO table + 9 Examples

Example 5: From SELECT query

You can insert multiple rows in a table in PostgreSQL using a SELECT query also. The syntax is as follows:

INSERT INTO table_name1 (column_list1)
SELECT column_list2
FROM table_name2
[WHERE condition];

In the above syntax,

• table_name1 is the table name where you want to insert the rows.
• column_list1 is the list of the columns from the table_name1 whose values you want to insert.
• table_name2 in the SELECT query is the table name from which you want to export the rows(data) to table_name1.
• column_list2 is the list of the columns selected by the SELECT query from the table table_name2.
• You can filter selected rows by adding a WHERE clause followed by a condition.
• NOTE: The column_list in the SELECT query must match the column_list specified in the INSERT INTO statement.

Example:

SELECT count(*) FROM device_data;

SELECT count(*) FROM west_device_data;

INSERT INTO device_data(first_name, last_name, email, gender, ip_address)
SELECT first_name, last_name, email, gender, ip_address
FROM west_device_data
WHERE west_device_data.id > 21;

SELECT count(*) FROM device_data;

Read Update query in PostgreSQL

Example 5: From another table

Using the SELECT query, the above method allows you to insert multiple rows into a table from another table in PostgreSQL. The syntax is the same as the above.

So, let’s practice some examples to handle the concept better.

Example:

SELECT count(*) FROM device_data;

SELECT count(*) FROM west_device_data;

INSERT INTO device_data(first_name, last_name, email, gender, ip_address)
SELECT first_name, last_name, email, gender, ip_address
FROM west_device_data
WHERE id < 21;

SELECT count(*) FROM device_data;

PostgreSQL INSERT Multiple Rows limit

You can insert a maximum of 1000 rows in a single statement in PostgreSQL. If you want to insert more than 1000 records, you need to run INSERT INTO statement multiple times.

Conclusion

In this PostgreSQL tutorial, we have learned about how PostgreSQL INSERT Multiple Rows in a single statement with examples.

You may like the following tutorials:

• PostgreSQL vs SQL Server: Detailed Comparison
• PostgreSQL ADD COLUMN + 17 Examples
• PostgreSQL DATE Format + Examples
• Postgres RegEx
• PostgreSQL add primary key