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 for using the INSERT statement, which have different syntax: to insert one or several individual records, or to insert the whole dataset of many records.

To insert several records one should use VALUES clause. 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 parenthesis 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:

INSERT INTO car_portal_app.a (a_int) VALUES (6);

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 keywords DEFAULT VALUES:

INSERT INTO car_portal_app.a DEFAULT VALUES;

And it is possible to insert multiple records using 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:

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.

It is possible to use the table in which the records are inserted, in the SELECT query. 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. But 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:

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

The UPDATE statement is used to change the data in the records of a table without changing their number. It has the following syntax:

UPDATE <table_name>
SET <field_name> = <expression>[, ...]
[FROM <table_name> [JOIN clause]]
[WHERE <condition>];

There are two ways of using the UPDATE statement. The first is similar to the simple SELECT statement, and is called sub-select. The second is based on other tables, and is similar to the SELECT statement from multiple tables. In most cases, the same result can be achieved by using any of those methods.

In PostgreSQL, only one table can be updated at a time. Other databases may allow the updating of multiple tables at the same time under certain conditions.

UPDATE t SET f = f+1 WHERE a = 5;

UPDATE car_portal_app.a u SET a_text =
  (SELECT b_text FROM car_portal_app.b
    WHERE b_int = u.a_int);

UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b WHERE a.a_text=b.b_text;

UPDATE car_portal_app.a SET a_int =
  (SELECT b_int FROM car_portal_app.b
    WHERE a.a_text=b.b_text)
WHERE a_text IN (SELECT b_text FROM car_portal_app.b);

UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b;

UPDATE car_portal_app.a SET a_int = b_int
  FROM car_portal_app.b
  WHERE b_int>=a_int;

UPDATE car_portal_app.a SET a_int = 0 RETURNING *;
 a_int | a_text
-------+--------
     0 | one
     0 | two
...

The DELETE statement is used to remove records from the database. As with UPDATE, there are two ways of deleting: using sub-select or using another table or tables. The sub-select syntax is as follows:

DELETE FROM <table_name> [WHERE <condition>];

Records that follow the condition will be removed from the table. If the WHERE clause is omitted, then all the records will be deleted.

Delete based on another tables is similar to using the FROM clause of the UPDATE statement. But instead of FROM, the USING keyword should be used because FROM is already used in the syntax of the DELETE statement:

DELETE FROM car_portal_app.a
USING car_portal_app.b WHERE a.a_int=b.b_int;

The preceding statement will delete all the records from a when there is a record in b with the same value of the numeric field. This is equivalent to:

DELETE FROM car_portal_app.a
WHERE a_int IN (SELECT b_int FROM car_portal_app.b);

As well as UPDATE and INSERT, the DELETE statement can return deleted rows when the RETURNING keyword is used:

DELETE FROM car_portal_app.a RETURNING *;
 a_int | a_text
-------+--------
     0 | one
     0 | two
...

Another statement that can also change the data but not the data structure is TRUNCATE. It clears the table completely and almost instantly. It has the same effect as the DELETE statement without the WHERE clause. So, it is useful on large tables:

TRUNCATE TABLE car_portal_app.a;