The INSERT statement is used to insert new data into tables in the database. The records are always inserted into only one table.

The INSERT statement has the following syntax:

INSERT INTO <table_name> [(<field_list>)]
{VALUES (<expression_list>)[,...]}|{DEFAULT VALUES}|<SELECT query>;

The name of the table into which the records are inserted is specified after the INSERT INTO keywords. There are two options to use the INSERT statement, which has a different syntax: to insert one or several individual records or to insert the whole dataset of many records.

To insert one or several records, the VALUES clause is used. The list of the values to insert is specified after the VALUES keyword. Items of the list correspond to the fields of the table according to their order. If it is not necessary to set the values for all the fields, the names of the fields whose values should be set are specified in parentheses after the table name. The skipped fields will then get their default values, if defined, or they will be set to NULL.

The number of items in the VALUES list must be the same as the number of fields after the table name:

car_portal=> INSERT INTO car_portal_app.a (a_int) VALUES (6);
INSERT 0 1

The output of the INSERT command when it has successfully executed is the word INSERT followed by the OID of the row that has been inserted (in case only one row was inserted and OIDs are enabled for the table, otherwise it is zero) and the number of records inserted. To know more about OIDs, refer to Chapter 12, The PostgreSQL Catalog, and System Administration Functions.

Another way to set default values to the field is to use the DEFAULT keyword in the VALUES list. If default is not defined for the field, a NULL value will be set:

INSERT INTO car_portal_app.a (a_text) VALUES (default);

It is also possible to set all fields to their default values using the keyword, DEFAULT VALUES:

INSERT INTO car_portal_app.a DEFAULT VALUES;

It is possible to insert multiple records using the VALUES syntax:

INSERT INTO car_portal_app.a (a_int, a_text) VALUES (7, 'seven'), (8,'eight');

This option is PostgreSQL-specific. Some other databases allow inserting only one row at a time.

In fact, in PostgreSQL, the VALUES clause is a standalone SQL command. Therefore, it can be used as a subquery in any SELECT query:

car_portal=> SELECT * FROM (VALUES (7, 'seven'), (8, 'eight')) v;
 column1 | column2
---------+---------
       7 | seven
       8 | eight
(2 rows)

When the records to insert are taken from another table or view, a SELECT query is used instead of the VALUES clause:

INSERT INTO car_portal_app.a SELECT * FROM car_portal_app.b; 

The result of the query should match the structure of the table: have the same number of columns of compatible types in the same order.

In the SELECT query, it is possible to use the table in which the records are inserted. For example, to duplicate the records in the table, the following statement can be used:

INSERT INTO car_portal_app.a SELECT * FROM car_portal_app.a;

By default, the INSERT statement returns the number of inserted records. It is also possible to return the inserted records themselves or some of their fields. The output of the statement is then similar to the output of the SELECT query. The RETURNING keyword, with the list of fields to return, is used for this:

car_portal=> INSERT INTO car_portal_app.a SELECT * FROM car_portal_app.b RETURNING a_int;
  a_int
 -------
      2
      3
      4
 (3 rows)

INSERT 0 3

In case a unique constraint is defined on the table where the rows are inserted, INSERT will fail if it tries to insert records conflicting with existing ones. However, it is possible to let INSERT ignore such records or update them instead.

Assuming that there is a unique constraint on the table b, consider the following example for the field b_int:

car_portal=> INSERT INTO b VALUES (2, 'new_two');
ERROR: duplicate key value violates unique constraint "b_b_int_key"
DETAIL: Key (b_int)=(2) already exists.

What if we want to change the record instead of inserting in case it exists already:

car_portal=> INSERT INTO b VALUES (2, 'new_two') 
  ON CONFLICT (b_int) DO UPDATE SET b_text = excluded.b_text 
  RETURNING *;
 b_int | b_text 
-------+---------
     2 | new_two
(1 row)

INSERT 0 1

Here, the ON CONFLICT clause specifies what has to happen in case there is already a record with the same value in the field a_int. The syntax is straightforward. The table alias excluded refers to the values that are being inserted. If it was necessary to refer to the values that were in the table, the table name would be used.