Data can be inserted into database tables, updated or deleted from the database. Respectively, statements are used for this: INSERT
, UPDATE
and DELETE
.
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.
The expression for a new value is the usual SQL expression. It is possible to refer to the same field in the expression. In that case the old value is used:
UPDATE t SET f = f+1 WHERE a = 5;
It is common to use a subquery in the UPDATE
statements. To refer to the updated table from a subquery the table should have an alias:
UPDATE car_portal_app.a u SET a_text = (SELECT b_text FROM car_portal_app.b WHERE b_int = u.a_int);
If the subquery returns no result, the field value is set to NULL
.
The WHERE
clause is similar to the one used in the SELECT
statement. If the WHERE
statement is not specified, all the records are updated.
The second way of updating rows in the table is to use the FROM
clause in a similar manner as it is done in the SELECT
statement:
UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b WHERE a.a_text=b.b_text;
All rows from a
, for which there are rows in b
with the same value of the text field, were updated. The new value for the numeric field was taken from the table b
. Technically, it is nothing but an inner join of the two tables. But the syntax here is different. Since table a
is not part of the FROM
clause, using the usual join
syntax is not possible and the tables are joined on the condition of the WHERE
clause. But if another table was used it would have been possible to join it to the table b
using the join syntax, inner or outer.
The FROM
syntax of the UPDATE
statement can seem more obvious in many cases. For example, the following statement performs the same changes to the table as the previous, but it is less clear:
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);
Another advantage of the FROM
-syntax is that in many cases it is much faster.
On the other hand, this syntax can have unpredictable results in cases where for a single record of the updated table there are several matching records from the tables of the FROM
clause.
For example:
UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b;
This query is syntactically correct. However, it is known that in the table b
there is more than one record. And which of them will be selected for every updated row is not determined, since no WHERE
condition is specified. The same happens when the WHERE
clause does not define the one-to-one matching rule:
UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b WHERE b_int>=a_int;
For each record of table a there is more than one record from table b
where, b_int
is more or equal to a_int
. That's why the result of this update is undefined. However, PostgreSQL will allow this to be executed.
For this reason, one should be careful when uses this way of doing updates.
The update query can return updated records if the RETURNING
clause is used as it is in the INSERT
statement:
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 ...
18.222.95.56