Inserting Data

---

The INSERT statement is used to insert new rows into existing tables.

Values to be inserted may be specified explicitly, as constants or expressions, or in the form of a subselect, see below.

The data to be inserted must be of a type compatible with the corresponding column definition.

If the length of the inserted data differs from that of the column definition, the data is handled as follows:

Data	Explanation
Character strings	If the inserted data is longer than the column definition, an error is reported and the INSERT operation fails (trailing spaces are truncated without error). If the inserted data is shorter than the column definition, it is padded to the right with spaces to the required length when inserted into a fixed-length character column. The inserted data is not padded when inserted into a VARCHAR or NCHAR VARYING column.
Decimal values	Decimal values which are longer than the column definition are truncated (not rounded) from the right to meet the column definition. Thus 12.3456 is inserted into DECIMAL(6,3) as 12.345. Decimal values which are shorter than the column definition are padded to the right of the decimal point with zeros. Thus 12.3 is inserted into DECIMAL(6,3) as 12.300.
Integer values	If the inserted data has more digits than the column definition or is outside the range of the definition, an error is reported and the INSERT operation fails.
Floating point values	Floating point values are converted to decimal by truncating the fractional part of the value as required by the scale of the decimal target. An error occurs if the scale of the target cannot accommodate the integral part of the value.
Datetime values	Date values are converted to timestamp by setting the hour, minute and second fields to zero. Time values are converted to timestamp by taking values for the year, month and day fields from CURRENT_DATE. Timestamp values are converted to date or time by discarding the field values that do not appear in the target.
Interval values	Single field interval values are converted to exact numeric by truncating decimal digits or by padding decimal digits with zeros. If any loss of leading precision occurs, or if overflow occurs, an error is raised.
Binary values	If the inserted data is longer than the column definition, an error is reported and the INSERT operation fails. If the inserted data is shorter than the column definition, and the column is fixed-length binary, an error is reported and the INSERT operation fails.

Inserting Explicit Values

The explicit INSERT statement has the general form:

 INSERT INTO table [(column-list)]
    VALUES (value-list);
 

Values in the value-list are inserted into columns in the column-list in the order specified.

The order of columns in the column-list need not be the same as the order in the table definition. Any columns in the table definition which are not included in the column-list are assigned NULL values, or the column default value if one is defined.

An explicit INSERT statement can only insert a single row.

For example:

Insert the values 'GW', 'Guinea-Bissau' and 'XOF' into the CODE, COUNTRY and CURRENCY_CODE columns respectively into the COUNTRIES table:

 INSERT INTO countries(code, country, currency_code)
    VALUES ('GW', 'Guinea-Bissau', 'XOF');
 

inserts the row:

CODE	COUNTRY	CURRENCY_CODE
GW	Guinea-Bissau	XOF

If you insert explicit values into all of the columns in a table, the column list can be omitted from the INSERT statement. The values specified are then inserted into the table in the order that the columns are defined in the table.

Thus the example above could also be written:

 INSERT INTO countries
    VALUES ('GW', 'Guinea-Bissau', 'XOF');

Inserting Results of Expressions

You can also insert the result of an expression into a table:

 INSERT INTO mimer_store.customers(customer_id,
                                   title, surname, forename,
                                   date_of_birth,
                                   address_1, address_2, town,
                                   postcode, country_code,
                                   email, password,
                                   registered)
    VALUES (DEFAULT,
            'Mr', 'Eriksson', 'Sven',
             mimer_store.cast_to_date('25/10/1953'),
            'Kungsgaten 64', 'Box 1713', 'Uppsala',
            '751 47', 'SE',
            'training@mimer.com', 'secret',
            CURRENT_DATE);

Inserting with a Subselect

Values to be inserted can also be specified in the form of a subselect, i.e. fetched from another table in the database.

 INSERT INTO formats
    SELECT 11, 'Book & Cassette', MAX(formats.category_id),
        MAX(display_order) + 10
    FROM formats JOIN categories
        ON formats.category_id = categories.category_id
    WHERE category = 'Books';
 

Inserting the result of a subselect can insert a number of rows into a table. If any of the rows are rejected (e.g. because of a duplicate primary or unique key), the whole INSERT statement fails and no rows are inserted.

Inserting Sequence Values

The value to be inserted can be the value of a sequence. The constructs that return the current value or next value of a sequence can be used as column values in the INSERT statement:

 INSERT INTO products(product, product_id)
    VALUES ('SQL Reference', NEXT VALUE FOR product_id_seq);
 
 INSERT INTO mimer_store_music.titles(item_id, artist_id)
    VALUES (CURRENT VALUE FOR mimer_store.item_id_seq, 500999);

Inserting NULL Values

The keyword NULL may be used to insert the NULL value into a column, provided that the column is not defined as NOT NULL:

 INSERT INTO tracks(item_id, track_no, track, length)
    VALUES (60099, 14, 'Bayamesa', NULL);
 

The NULL indicator is implicitly inserted into columns when no value is given for that column and the column definition does not include a default value.

Thus, the following INSERT statement will give the same results as the example above:

 INSERT INTO tracks(item_id, track_no, track)
    VALUES (60099, 14, 'Bayamesa');

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se