Practical PostgreSQL
Prev		Next

INSERT

Name

INSERT -- Inserts new rows into a table.

Synopsis

INSERT INTO table [ ( column [, ...] ) ]
            { DEFAULT VALUES |
              VALUES ( value [, ...] ) |
              query }

Parameters

table: The table into which you are inserting data.
column: A column for which a value will be specified. The name must match a column table, though these columns need not be listed in their literal order within the table.
value: A constant or expression to insert into a column within table. This value is associated with the corresponding column in the column list if a column list was specified (columns in the column list correspond in a one-to-one fashion with expressions in the value list). If the expression for each column is not of the correct data type, automatic type coercion will be attempted. If this fails, the INSERT will fail completely.
query: A valid SQL SELECT statement. The number of columns returned by the query must match the number of columns you are inserting, as well as be of a compatible data type.

Results

INSERT oid 1: The message returned if one row of data is inserted correctly. The oid is the object identifier of the newly inserted row.
INSERT 0 #: The message returned if more than one row is inserted. The # symbol represents how many rows were updated in total.

Description

Use the INSERT command to add new rows into a table. This can be done either one row at a time, or in sets. Used with the VALUES keyword, an INSERT statement can only insert one row of data. To insert multiple rows, you can instead supply a query. Results from the query are then fed into the INSERT command's target table.

If an incorrect data type is provided for a field on insertion, PostgreSQL will attempt to automatically coerce it into the appropriate type. If it cannot, the INSERT will fail.

When inserting values into columns (instead of whole rows), the columns can be listed in any order; however, the values for those columns will need to be listed in the same order.

Note: If you leave out values for any fields in your table, the database will automatically do one of two things. Fields for which you have not specified a default value will be set to NULL. Fields for which you have specified a default value will be set to their defaults.

Examples

The following example inserts a single row into the employees table:

booktown=# INSERT INTO employees
booktown-#             VALUES (106, 'Hall', 'Timothy');
INSERT 3752064 1

Alternatively, you can insert only an ID number and last name, and not a first name, by specifying a target column list preceding the VALUES clause. This results in a NULL value for the first_name column in the new row:

booktown=# INSERT INTO employees (id, last_name)
booktown=#             VALUES (108, 'Williams');
INSERT 3752065 1

The next example inserts all 15 rows from the books table into the book_backup table by providing a query from which to insert data:

booktown=# INSERT INTO book_backup
booktown-#             SELECT * FROM books;
INSERT 0 15

Prev	Home	Next
GRANT	Up	LISTEN