PostgreSQL INSERT Statement

In PostgreSQL, the INSERT statement is used to insert one or more new rows into a table.

PostgreSQL `INSERT` syntax

To insert a new row into a table in PostgreSQL, use the following syntax of INSERT:

INSERT INTO table_name(column1, column2, …)
VALUES
  (value11, value12, …) [, (value21, value22, …), ...]
[ON CONFLICT conflict_target conflict_action]
[RETURNING expr];

Explanation:

The INSERT INTO and VALUES are keywords
The table_name is the name of the table into which the new row is to be inserted.
The (column1, column2, …) is a list of columns, where are the names of columns separated by commas.
The (value11, value12, …) is a list of values, where are the values for each column separated by commas. The values in the value list correspond to the columns in the column list .
To insert multiple rows of data at once, use multiple comma-separated lists of values.
The ON CONFLICT is used to implement upsert operations in PostgreSQL .
The RETURNING clause is optional. It is used to return information about the new inserted row. The expr can be a list columns or expressions, etc. You can use * representing all columns.

With RETURNING Clause

The INSERT statement has an optional RETURNING clause that returns the new inserted rows. If there is a RETURNING clause specified, the INSERT statement returns new rows according to the RETURNING clause, otherwise it returns the number of rows inserted.

The RETURNING clauses can take the following forms:

To return specified columns, use a list of columns. Multiple columns are separated by commas.

RETURNING column1
RETURNING column1, column2

You can also use AS to alias column names:

RETURNING column1 AS column1_new_1
RETURNING column1 AS column1_new_1, column2 AS column1_new_2

To return all columns of the new inserted rows, use an asterisk (*).
```
RETURNING *
```
Returns the value evaluated by an expression
```
RETURNING expr
```

Without `RETURNING` Clause

The return value of a INSERT statement without a RETURNING clause is in the following form:

INSERT oid count

Here:

The oid is an object identifier. PostgreSQL internally uses oid as the primary key for its system tables. Typically, INSERT statements return a oid 0.
The count is the number of rows inserted by the INSERT statement.

PostgreSQL `INSERT` Examples

We are going to demonstrate the following example in the testdb database. Please use the following statement to create the testdb database:

CREATE DATABASE testdb;

Select the testdb database as the current database:

\c testdb;

To demonstrate, we need to create a new table, named student.:

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  gender CHAR(1) NOT NULL,
  birthday DATE,
  notes VARCHAR(255)
);

Insert a single row into the table

The following statement inserts a new row into the student table:

INSERT INTO student(name, gender)
VALUES ('Tom', 'M');

The statement returns the following output:

INSERT 0 1

Here, 1 in INSERT 0 1 indicates that one row has been inserted into the student table.

Let’s look at the rows in the student table using the following SELECT statement:

SELECT * FROM student;

 id | name | gender | birthday | notes
----+------+--------+----------+-------
  1 | Tom  | M      |          |

We saw:

The value of the id column is 1. This is because the id column is a SERIAL column, PostgreSQL automatically generates a sequence value.
The birthday and notes column values are nulls. Because they have no NOT NULL constraints, PostgreSQL uses NULL inserts into these columns.

Insert a single row into a table and Returns the row

The following statement inserts a new row into the student table and returns the inserted row:

INSERT INTO student(name, gender)
VALUES ('Lucy', 'F')
RETURNING *;

 id | name | gender | birthday | notes
----+------+--------+----------+-------
  2 | Lucy | F      |          |

Here, since the INSERT statement has a RETURNING * clause, the statement returns all the columns in the new row. If we only want to return one or more of these columns, please specify the columns in the RETURNING clause, as follows:

INSERT INTO student(name, gender)
VALUES ('Jack', 'M')
RETURNING id AS "Student ID", name, gender;

The statement returns the following output:

 Student ID | name | gender
------------+------+--------
          3 | Jack | M

Here, we specified id, name and gender three columns in the RETURNING clause, and specified an alias Student ID for id.

Insert multiple rows into a table

You can insert multiple rows using a single INSERT statement, as follows:

INSERT INTO student(name, gender)
VALUES ('Jim', 'M'), ('Kobe', 'M'), ('Linda', 'F')
RETURNING *;

 id | name  | gender | birthday | notes
----+-------+--------+----------+-------
  4 | Jim   | M      |          |
  5 | Kobe  | M      |          |
  6 | Linda | F      |          |

Here, we have inserted 3 rows into the student table using a single INSERT statement.

Insert a date value

To insert date values into a column of DATE type, use a date string in the 'YYYY-MM-DD' format.

To insert a row with birthday data into the student table, use the following statement:

INSERT INTO student (name, gender, birthday)
VALUES('Alice', 'F', '2012-04-21')
RETURNING *;

Output:

 id | name  | gender |  birthday  | notes
----+-------+--------+------------+-------
  7 | Alice | F      | 2012-04-21 |

Conclusion

The PostgreSQL INSERT statement is used to insert one or more new rows into a table. The INSERT statement with a RETURNING clause returns information about new rows inserted, otherwise it returns the number of rows inserted.

PostgreSQL INSERT syntax