The following is the syntax of the INSERT INTO command, when used to insert new values, which is described in detail in Table 4-4 :

  INSERT INTO table_name 
         [ ( column_name [, ... ] ) ]
         VALUES ( value [, ... ] )

Table 4-4. INSERT INTO ... VALUES Syntax

Element

Description

INSERT INTO table_name

The SQL command which 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. If omitted, PostgreSQL assumes that the values following the VALUES clause apply to the literal left-to-right arrangement of columns in the table called table_name .

VALUES

The SQL clause which instructs PostgreSQL to expect a grouped expression of values to follow.

( values [, ...] )

The required grouped expression which 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 must be of the data type of the column which 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 if one is specified (or the NULL value, if there is no default) for each omitted value.

To demonstrate, Example 4-15 illustrates the insertion of some new books into Book Town's books table.

Example 4-15. 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

This SQL statement specifies to insert an id of 41472 , a title of Practical PostgreSQL , an author identifier of 1212 , and a subject identifier of 4 . The returned INSERT statement indicates that the insertion was successful. The numbers following the INSERT result in Example 4-15 are the OID (object identifier) of the freshly inserted row, and the number of rows that were inserted (in this case, 1).

Notice that the optional column target list is specified identically to the structure of the table. In this case, omitting that grouped expression would have an no effect on the statement, since it defaults to the assumption that you are inserting in that order if unspecified. You can also re-arrange the names of the columns in the grouped target list if you wish to specify the values in a different order following the VALUES clause, as demonstrated in Example 4-16 .

Example 4-16. 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