The following is the syntax of the INSERT INTO command, when used to insert
new values, which is subsequently described in detail:
INSERT INTO
table_name
[ (
column_name
[, ...] ) ]
VALUES (
value
[, ...] )
-
table_name
-
The INSERT SQL command initiates an insertion of data into the table called
table_name
.
-
(
column_name
[, ...] )
-
An optional grouped expression which describes the targeted columns for the insertion.
-
VALUES
-
The SQL clause which instructs PostgreSQL to expect a grouped expression of values to follow.
-
(
value
[, ...] )
-
The required grouped expression that describes the values to be inserted. There should be one
value
for each specified column, separated by commas. These values may be expressions themselves (e.g., an operation
between two values), or constants.
Each
value
following the VALUES clause must be of the same data type as the column
it is being inserted into. If the optional column-target expression is omitted, PostgreSQL will expect there to be
one value for each column in the literal order of the table's structure. If there are fewer values to be inserted than
columns, PostgreSQL will attempt to insert a default value (or the
NULL value, if there is no default) for each omitted value.
To demonstrate, Example 4-16 illustrates the insertion of a new book
into Book Town's books table.
Example 4-16. Inserting new values into the books table
booktown=#
INSERT INTO books (id, title, author_id, subject_id)
booktown-#
VALUES (41472, 'Practical PostgreSQL', 1212, 4);
INSERT 3574037 1
The SQL statement in Example 4-16 inserts a new book with an id of 41472, a
title of
Practical PostgreSQL
, an author identifier of 1212, and a subject identifier of 4. Note the
feedback beginning with INSERT, which indicates that the insertion was successful. The first number
following INSERT is the OID (object identifier) of the freshly inserted row. The second number following
INSERT represents the number of rows inserted (in this case, 1).
Notice that the optional column target list is specified identically to the physical structure of the table, from
left to right. In this case, omitting the grouped expression would have no effect on the statement since the
INSERT statement assumes that you are inserting values in the natural order of the
table's columns. You can re-arrange the names of the columns in the grouped column target list if you wish to specify
the values in a different order following the VALUES clause, as demonstrated in Example 4-17.
Example 4-17. Changing the order of target columns
booktown=#
INSERT INTO books (subject_id, author_id, id, title)
booktown-#
VALUES (4, 7805, 41473, 'Programming Python');
INSERT 3574041 1
