INSERTstatement 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:
Character strings If the inserted data is longer than the column definition, an error is reported and the
INSERToperation 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
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.3456is inserted into
12.345.Decimal values which are shorter than the column definition are padded to the right of the decimal point with zeros. Thus
12.3is inserted into
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
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
INSERToperation fails.If the inserted data is shorter than the column definition, and the column is fixed-length binary, an error is reported and the
Inserting Explicit Values
INSERTstatement 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
NULLvalues, or the column default value if one is defined.
INSERTstatement can only insert a single row.
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:
If you insert explicit values into all of the columns in a table, the column list can be omitted from the
INSERTstatement. 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', 'firstname.lastname@example.org', '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
INSERTstatement 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
INSERTstatement: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
NULLmay be used to insert the
NULLvalue 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);
NULLindicator 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
INSERTstatement 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