PostgreSQL INSERT Multiple Rows (Complete tutorial)

In this PostgreSQL tutorial, we will discuss, that how PostgreSQL INSERT Multiple Rows in a single statement and will cover the following topic:

PostgreSQL INSERT Multiple Rows
PostgreSQL INSERT Multiple Rows RETURNING
PostgreSQL INSERT Multiple Rows limit
PostgreSQL INSERT Multiple Rows ON CONFLICT
PostgreSQL INSERT Multiple Rows IF NOT EXISTS
PostgreSQL INSERT Multiple Rows from array
PostgreSQL INSERT Multiple Rows from SELECT query
PostgreSQL INSERT Multiple Rows from another table

Table of Contents

PostgreSQL INSERT Multiple Rows

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

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 the columns has to be specified whose values you want to insert into the table within parenthesis.
comma-separated multiple value-lists has to be supplied after the keword 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 respectively should be in the same order.

In the command tag,

OID is an object identifier. PostgreSQL uses the OID as a PRIMARY KEY for the tables in the system. The INSERT statement returns 0 as the OID value.
And the COUNT is the number of rows that are 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

PostgreSQL INSERT Multiple Rows — PostgreSQL CREATE TABLE

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;

Read PostgreSQL ALTER TABLE + 19 Examples

PostgreSQL INSERT Multiple Rows RETURNING

You can also return the inserted rows information in PostgreSQL by 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 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;

PostgreSQL INSERT Multiple Rows RETURNING

Read PostgreSQL DATE Functions with Examples

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.

PostgreSQL INSERT Multiple Rows 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,

Lets get to know about the UPSERT features:
- The UPSERT is referred to as merge in relational databases, as 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 existed 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, the row already existed will get updated according to the action, the action can be any UPDATE statement or DO NOTHING keywords, the UPDATE statement will update the existed row and the later one will do nothing, that is the row will neither 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 *;

PostgreSQL INSERT Multiple Rows ON CONFLICT

Read PostgreSQL DROP COLUMN

PostgreSQL INSERT Multiple Rows IF NOT EXISTS

You can insert multiple rows in a table if not existed 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, lets 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 *;

PostgreSQL INSERT Multiple Rows IF NOT EXISTS

There is one more way, through which you can insert multiple rows in a table if not existed 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, INSERT INTO statement is used to insert selcted rows into the table table_name from the temporary table data.
Here, Nested SELECT statement is used to select the data which is 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 *;

PostgreSQL INSERT Multiple Rows IF NOT EXISTS using SELECT

PostgreSQL INSERT Multiple Rows from array

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

Read PostgreSQL INSERT INTO table + 9 Examples

PostgreSQL INSERT Multiple Rows from SELECT query

You can insert multiple rows in a table in PostgreSQL using 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 name of the table 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 name of the table from where you want to export the rows(data) to the table_name1.
column_list2 is the list of the columns to be selected by the SELECT query from the table table_name2.
You can also filter selected-rows by adding a WHERE clause followed by a condition.
NOTE – The column_list in the SELECT query must match with 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;

PostgreSQL INSERT Multiple Rows from SELECT query

Read Update query in PostgreSQL

PostgreSQL INSERT Multiple Rows from another table

You can insert multiple rows into a table from another table in PostgreSQL by the above method, using SELECT query. The syntax is as same as the above.

So, lets practice some examples to get a better hand on the concept.

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;