PostgreSQL INSERT INTO table + 9 Examples

In this PostgreSQL tutorial, we will discuss, about PostgreSQL INSERT INTO table statement to populate data into an existing table and will cover the following topic:

• PostgreSQL INSERT INTO table
• PostgreSQL INSERT INTO table multiple rows
• PostgreSQL INSERT INTO table with auto_increment
• PostgreSQL INSERT INTO table with FOREIGN KEY
• PostgreSQL INSERT INTO table IF NOT EXISTS
• PostgreSQL INSERT INTO table from SELECT query
• PostgreSQL INSERT INTO table from another table
• PostgreSQL INSERT INTO table from another database
• PostgreSQL INSERT INTO table from csv
• PostgreSQL INSERT INTO table from json

PostgreSQL INSERT INTO table

You can insert a new row into a table in PostgreSQL by using the INSERT INTO statement. The syntax is as follow:

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...);

In the above syntax:

• table_name specifies the name of the table where you want to insert data followed by the comma-seperated column-list (required columns or all columns) of that table enclosed within parentheses.
• Then, comma_seperated value-list enclosed within parentheses is supplied after VALUES keyword.
• 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:

 -- Lets create a table
CREATE TABLE player_data (
player_name VARCHAR(50),
gender VARCHAR(50),
country VARCHAR(50),
weight_kg INT,
height_cm INT
);

\d

\d player_data

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Sylas Salvage', 'Female', 'Mexico', 57, 135);

SELECT * FROM player_data;

You can also return the inserted row(s) information by using the RETURNING clause in the INSERT INTO statement. The syntax is as follows:

-- For returning entire inserted row

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING *;            

-- For returning specific columns:

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING columnK, columnP, ...;

-- For renaming the returned value

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING output_expression AS output_name;

In the above syntax,

• The asterisk (*) will return the entire inserted row(s).
• If you want to return just some information of the inserted row, then you can specify the column(s) after RETURNING clause.
• You can also rename the returned value in RETURNING clause by using the AS keyword followed by the name of the output.

Example:

SELECT * FROM player_data;

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Rockie Ethridge', 'Female', 'Canada', 67, 179)
RETURNING *;


-- For Returning specific columns of the inserted row

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Lorin Batkin', 'Male', 'Brazil', 83, 183)
RETURNING player_name, country;

Read PostgreSQL ALTER TABLE + 19 Examples

PostgreSQL INSERT INTO table multiple rows

You can insert multiple rows in a table in PostgreSQL by specifying comma-separated value lists after the VALUES keyword 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];         -- You can also add RETURNING clause

In the above syntax, You can also use RETURNING clause, to get the inserted rows as the output.

Example:

SELECT * FROM player_data;

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES
('Morton Cruxton', 'Male', 'China', 85, 197),
('Nelson Croser', 'Female', 'Czech Republic', 75, 135),
('Prentice Arnould', 'Male', 'China', 49, 181),
('Elsy Maharry', 'Male', 'United States', 63, 181)
RETURNING player_name, country;

SELECT * FROM player_data;

This is how to insert multiple rows to a table in PostgreSQL.

Read PostgreSQL DATE Functions

PostgreSQL INSERT INTO table with auto_increment

You can insert data into a table having a column auto_increment by using the DEFAULT keyword in place of the value in the value-list corresponding to the autoincrement column in the column list in the INSERT INTO statement. The syntax is as follow:

INSERT INTO table_name (autoincrement_column, column2, column3, ...)
VALUES (DEFAULT, value2, value3, ...);

Or you can avoid specifying the column_name of the autoincrement column in the column-list and hence, no need to specify any value in the value-list in the INSERT INTO statement.

Example:

SELECT * FROM player_data;

INSERT INTO player_data (p_id, player_name, gender, country, weight_kg, height_cm)
VALUES (DEFAULT, 'Nelson Croser', 'Female', 'Czech Republic', 75, 135)
RETURNING p_id;

SELECT * FROM player_data;

CREATE TABLE player_data (
p_id SERIAL PRIMARY KEY,
player_name VARCHAR(50),
gender VARCHAR(50),
country VARCHAR(50),
weight_kg INT,
height_cm INT);

\d player_data
 
INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Elsy Maharry', 'Male', 'United States', 63, 181);

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Rockie Ethridge', 'Female', 'Canada', 67, 179)
RETURNING p_id;

SELECT * FROM player_data;

This is how to insert into table with auto_increment in PostgreSQL.

Read PostgreSQL ADD COLUMN

PostgreSQL INSERT INTO table with FOREIGN KEY

You can insert data (row(s)) in a table with FOREIGN KEY constraint referencing other tables only if the data given to the FOREIGN KEY column in the REFERENCING TABLE (Child table) specified in the INSERT INTO statement is already present in the PRIMARY KEY column of the REFERENCED TABLE (parent table), else it will lead to an error stating, “Referencing_table violates foreign key constraint”.

Example:

Lets create two tables for the example, as given below:

CREATE TABLE sport_data (
sport_id SERIAL PRIMARY KEY,
sport_name VARCHAR(80));

CREATE TABLE player_data (
p_id SERIAL PRIMARY KEY,
player_name VARCHAR(50),
gender VARCHAR(50),
country VARCHAR(50),
weight_kg INT,
height_cm INT,
sport_id INT,
CONSTRAINT fk_sport
FOREIGN KEY(sport_id)
REFERENCES sport_data);

\d

Now, Do the task:

-- Lets try to add a row in player_data (child/referencing) table

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm, sport_id)
VALUES ('Trevar Kobelt', 'Male', 'Morocco', 77, 198, 2)
RETURNING p_id;


-- Now, Lets add some rows in the sport_data (parent/referenced) table

INSERT INTO sport_data (sport_name)
VALUES
('100m sprint'),
('Wrestling'),
('Swimming'),
('Triple Jump'),
('Long Jump'),
('High Jump');

SELECT * FROM sport_data;


-- Now lets try again to insert a row in player_data table

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm, sport_id)
VALUES ('Trevar Kobelt', 'Male', 'Morocco', 77, 198, 2);

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm, sport_id)
VALUES
('Charita Maudling', 'Male', 'United States', 76, 142, 2),
('Sylas Salvage', 'Female', 'Mexico', 57, 135, 5);

SELECT * FROM player_data;

This is how to INSERT INTO table with FOREIGN KEY in PostgreSQL.

Read PostgreSQL vs SQL Server: Detailed Comparison

PostgreSQL INSERT INTO table IF NOT EXISTS

You can avoid ERROR when duplication of data while inserting row into the table, by checking if the column value specified in the INSERT statement with UNIQUE or EXCLUSION constraint already exists in the previous rows or not.

You can use UPSERT in PostgreSQL by adding the ON CONFLICT clause in the INSERT INTO statement to perform the task. The syntax is as follow:

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (columnS)
DO NOTHING;

In the above syntax, the ON CONFLICT will check the values in the column given as columnS matches the value specified in the INSERT INTO statement, and If it is true, then do the action followed by the ON CONFLICT clause, which is DO NOTHING here, which results in skipping the INSERT INTO statement that will avoid ERROR.

NOTE – 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.

Example:

SELECT * FROM player_data;

INSERT INTO player_data (p_id, player_name, gender, country, weight_kg, height_cm, sport_id)
VALUES (2, 'Trevar Kobelt', 'Male', 'Morocco', 77, 198, 2)
;


-- Now, lets try again the insertion using UPSERT

INSERT INTO player_data (p_id, player_name, gender, country, weight_kg, height_cm, sport_id)
VALUES (2, 'Trevar Kobelt', 'Male', 'Morocco', 77, 198, 2)
ON CONFLICT (p_id)
DO NOTHING;

SELECT * FROM player_data;

This is how to insert into table if not exists in PostgreSQL.

Read PostgreSQL DROP COLUMN

PostgreSQL INSERT INTO table from SELECT query

You can populate data into a table in PostgreSQL by using a SELECT query to select the data from some table to be returned as the VALUES in the INSERT INTO statement. The syntax is as follows:

INSERT INTO table1 (column11, column12, ...)
SELECT column21, column22, ...
FROM table2
[WHERE condition];

In the above syntax,

• table1 is the name of the table where you want to populate data.
• table2 is the name of the table from where you want to populate data.
• The convention for the naming of the column names here is like column11 and column21 means column1 of table1 and column1 of table2 respectively.
• You can also add WHERE cause in the SELECT staement for selecting the values follow some condition.

Example:

SELECT * FROM player_data;

SELECT * FROM new_player_data;
 
INSERT INTO new_player_data (player_name, gender, country, weight_kg, height_cm)
SELECT player_name, gender, country, weight_kg, height_cm
FROM player_data
WHERE weight_kg > 50;

SELECT * FROM new_player_data;

This is an example of PostgreSQL INSERT INTO table from SELECT query.

Read How to create database in PostgreSQL

PostgreSQL INSERT INTO table from another table

You can populate data into a table from another table in PostgreSQL by using a SELECT statement with the INSERT INTO statement for specifying the values to the columns. The syntax is as follows:

INSERT INTO table1 (primary_key_column1, column11, column12, ...)
SELECT primary_key_column2, column21, column22, ...
FROM table2
[WHERE condition];

The above Syntax is same as given in the topic: PostgreSQL INSERT INTO table from SELECT query, so lets do an example to clear the concept well.

Example:

SELECT * FROM player_data;

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
SELECT player_name, gender, country, weight_kg, height_cm
FROM new_player_data
WHERE p_id < 3;

SELECT * FROM player_data;

This is how to insert into a table from another table in PostgreSQL.

Read PostgreSQL INSERT Multiple Rows

PostgreSQL INSERT INTO table from another database

You can also populate data into the table from the table present in another database in PostgreSQL by using the dblink keyword, which executes a query in a remote database. The syntax is as follows:

INSERT INTO table1
SELECT *
 FROM
dblink(connname, sql_query [, bool fail_on_error]) returns setof record;


-- Here, after rewriting the arguments and return values of the dblink

INSERT INTO table1
SELECT * FROM
dblink('dbname=postgres hostaddr=xxx.xxx.xxx.xxx dbname=mydb user=postgres',
'SELECT column21, column22, ... FROM table2')
AS t1(column21 type, column22 type, ...)
[WHERE condition];

In the above syntax,

• connname is the connection string that specifies the connection to the remote database. The connection string includes dbname (remote database name), hostaddr (host address), dbname (local database in remote server), and user (local database user).
• sql_query can be any SQL query that return rows.
• You have to specify the returned data’s columns with their type.
• And you can also add the WHERE clause to the returned data.

Example:

PostgreSQL INSERT INTO table from csv

You can populate data into the table in PostgreSQL from a CSV (comma-seperated values) file by using the COPY keyword that copies the data from a csv file source. The syntax is as follow:

COPY table_name(column1, column2, ...)
FROM 'file_path\file_name.csv'
DELIMITER ','
CSV HEADER;

In the above syntax,

• file_path is the path of the file in the local machine (OS).
• fine_name is the name of the csv file followed by the extension of the file that is .csv.
• DELIMITER is the keword that specifies the delimiter of the data values, here it is comma (,) as for CSV.
• At the end CSV HEADER keywords specifies that to not consider the 1st line as it is the header not values.
• In a csv format, rows are differentiated by the newline and the delimiter (comma) specifies different values (column values) in a row.

Example:

SELECT * FROM new_player_data;

COPY new_player_data(player_name, gender, country, weight_kg, height_cm)
FROM 'C:\Users\Public\Data\new_players.csv'
DELIMITER ','
CSV HEADER;

SELECT * FROM new_player_data;

The above script we can use to INSERT INTO table from csv in PostgreSQL.

Read PostgreSQL DROP TABLE + Examples

PostgreSQL INSERT INTO table from json

You can populate data into the table from a JSON formatted data object in PostgreSQL by first creating a JSON type object and then using json_populate_recordset() to insert multiple records from JSON object to the table. The syntax is as follow:

WITH json_obj (doc) AS
( VALUES (
'data_in_json_format'::json))
INSERT INTO table_name (player_name, gender, country, weight_kg, height_cm)
SELECT obj.* FROM json_obj l CROSS JOIN lateral
json_populate_recordset(NULL::table_name, doc) AS obj;

In the above syntax,

• WITH keyword is creating a JSON object with the name json_obj as specified, with an argument of document type (doc).
• data_in_json_format specify multiple records in JSON format enclosed with single quotes, followed by the explicit type specification to json using ::json.
• json_populate_recordset(NULL::table_name, doc) is used to insert multiple JSON records and generates a relational representation of the JSON object which is specified as obj from the JSON object json_obj.

Example:

SELECT * FROM player_data;

WITH players_json (doc) AS
( VALUES (
'[{
"player_name": "Carolus Kenworth",
"gender": "Male",
"country": "France",
"weight_kg": 59,
"height_cm": 137
}, {
"player_name": "Orson Lince",
"gender": "Female",
"country": "Japan",
"weight_kg": 86,
"height_cm": 180
}, {
"player_name": "Armando Gut",
"gender": "Male",
"country": "Afghanistan",
"weight_kg": 69,
"height_cm": 172
}, {
"player_name": "Rosa Ruppel",
"gender": "Male",
"country": "Indonesia",
"weight_kg": 52,
"height_cm": 139
}, {
"player_name": "Annissa Gibbard",
"gender": "Male",
"country": "Brazil",
"weight_kg": 81,
"height_cm": 175
}]'::json))
INSERT INTO player_data(player_name, gender, country, weight_kg, height_cm)
SELECT p.* FROM players_json l CROSS JOIN lateral
json_populate_recordset(NULL::player_data, doc) AS p;

SELECT * FROM player_data;

The above script we can use to INSERT INTO table from json in PostgreSQL.

You may like the following PostgreSQL tutorial:

• PostgreSQL DATE Format + Examples
• PostgreSQL Export Table to CSV
• Update query in PostgreSQL
• PostgreSQL CREATE INDEX

In this PostgreSQL tutorial, we have learned about the  PostgreSQL INSERT INTO table   statement to populate data into an existing table and have covered the following topic:

• PostgreSQL INSERT INTO table
• PostgreSQL INSERT INTO table multiple rows
• PostgreSQL INSERT INTO table with auto_increment
• PostgreSQL INSERT INTO table with FOREIGN KEY
• PostgreSQL INSERT INTO table IF NOT EXISTS
• PostgreSQL INSERT INTO table from SELECT query
• PostgreSQL INSERT INTO table from another table
• PostgreSQL INSERT INTO table from another database
• PostgreSQL INSERT INTO table from csv
• PostgreSQL INSERT INTO table from json