In this tutorial, we will learn to insert a single row as well as multiple rows using the INSERT statement in PostgreSQL. It is very similar to other database systems. So, let’s get started!
Also Read: How to Create a Database?
PostgreSQL INSERT Statement
The INSERT statement is one of the DML commands in DBMS. It can be used to insert a single record as well as multiple records in a single query.
There are a few things to note before using the insert statement:
- table name: The most important thing is to specify a table name, of course. There are multiple tables in a single database. So, it is obvious to specify a table name to insert data into a specific target.
- column names: If you want to insert values for particular columns in a table, make sure you specify column names in an insert query and write values in the same order as the column names. If you want to insert values in all columns, you may skip writing column names.
- Row data in brackets: The row values must be enclosed within the rounded brackets.
- comma separated values: Every value must be separated from each other by a comma. In case of multiple rows, each row must be separated by a comma too.
That’s it. If you are a beginner and remember these things, you are good to go!
Syntax of PostgreSQL INSERT Statement
Following is the syntax to insert a single row into the table.
INSERT INTO table
VALUES(val1, val2,val3);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, it is considered that the table has three columns and you want to insert values in all three columns.
To insert values in particular columns, use the following syntax:
INSERT INTO table(col2,col3)
VALUES(val2,val3);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Let’s say the first column is of type serial id, therefore we will not need to insert a value for that column. Therefore, we will mention only the remaining column names and insert values for those columns only.
To insert multiple records, use the following syntax:
INSERT INTO table(col2,col3) VALUES
(val2,val3), (val12, val13), (val22,val23);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, multiple row values are separated using the comma.
Don’t forget the semicolon at the end to indicate the end of the query.
On inserting a row in the table, PostgreSQL returns the oid along with the number of records inserted in the following form-
INSERT 0 1By default, PostgreSQL creates a table without oid. Therefore, we will get 0 all the time. The second value is the number of records inserted into the table. To know more about oid, refer to this documentation.
Examples of the PostgreSQL INSERT Statement
Let’s now create a table to insert some value into it.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary NUMERIC(10,2),
hire_date DATE
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, we have created a table to store the employee information. The first column here is of type serial, therefore we will insert values for only other columns. We have a separate tutorial on creating a table in PostgreSQL if you want to read it.
Let’s now insert a single record into the ’employees’ table:
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('John Doe', 'Marketing', 50000.00, '2022-01-01');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see in the highlighted area, the query returns the 0 1, which means 1 record is inserted.
Let’s now insert multiple records:
INSERT INTO employees (name, department, salary, hire_date)
VALUES
('Jane Smith', 'Sales', 60000.00, '2022-02-01'),
('Bob Johnson', 'Engineering', 75000.00, '2022-03-01'),
('Sarah Lee', 'Finance', 65000.00, '2022-04-01');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, now we got 0 3, which means three records have been inserted into the table.
RETURNING Clause in PostgreSQL INSERT Statement
The INSERT statement also provides us with the optional clause RETURNING which returns the information about the inserted row/rows. You can return the inserted row names, ids or entire rows. Let’s see its syntax.
INSERT INTO table(col2,col3) VALUES
(val2,val3), (val12, val13), (val22,val23)
RETURNING col2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the query will return the values of col2 after completing the insertion operation.
If you want to return the whole row, you can use the * symbol after the RETURNING keyword. You can also use the alias for the returning column value using the following syntax-
INSERT INTO table(col2,col3) VALUES
(val2,val3), (val12, val13), (val22,val23)
RETURNING col2 as ID;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Examples of the RETURNING Clause
Let’s now see an example of the RETURNING clause.
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('Emily Jones', 'HR', 55000.00, '2022-05-01')
RETURNING id, name, department, salary, hire_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, we are returning the values of all columns.
As you can see, all the values are printed. However, if you want to see all column values, use the asterisk (*) sign as shown below.
INSERT INTO employees (name, department, salary, hire_date)
VALUES
('Tom Davis', 'Marketing', 55000.00, '2022-07-01'),
('Kate Lee', 'Sales', 65000.00, '2022-08-01'),
('Mike Brown', 'Engineering', 70000.00, '2022-09-01')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, using the * symbol will return all column values for all records.
Conclusion
In this tutorial, we learned how to insert single or multiple records into the table along with the RETURNING clause. Note that, the returning clause is an extension to the INSERT statement provided by PostgreSQL over the SQL standard.
References
PostgreSQL official documentation on insert statement