6.2. INSERT

6.2. `INSERT`

Used forInserting rows of data into a table

Available inDSQL, ESQL, PSQL

Syntax

   |INSERT INTO target
   |  {DEFAULT VALUES | [(<column_list>)] <value_source>}
   |  [RETURNING <returning_list> [INTO <variables>]]
   | 
   |<column_list> ::= colname [, colname ...]
   | 
   |<value_source> ::= VALUES (<value_list>) | <select_stmt>
   | 
   |<value_list> ::= <value> [, <value> ...]
   | 
   |<returning_list> ::= <ret_value> [, <ret_value> ...]
   | 
   |<ret_value> ::= colname | <value>
   | 
   |<variables> ::= [:]varname [, [:]varname ...]

Table 6.13. Arguments for the INSERT Statement Parameters

Argument	Description
target	The name of the table or view to which a new row, or batch of rows, should be added
colname	Column in the table or view
value	An expression whose value is used for inserting into the table or for returning
ret_value	The expression to be returned in the `RETURNING` clause
varname	Name of a PSQL local variable

DescriptionThe INSERT statement is used to add rows to a table or to one or more tables underlying a view:

If the column values are supplied in a VALUES clause, exactly one row is inserted
The values may be provided instead by a SELECT expression, in which case zero to many rows may be inserted
With the DEFAULT VALUES clause, no values are provided at all and exactly one row is inserted.

ⓘ

Restrictions

Columns returned to the NEW.column_name context variables in triggers should not have a colon (:) prefixed to their names
No column may appear more than once in the column list.

☝

ALERT : BEFORE INSERT Triggers

Regardless of the method used for inserting rows, be mindful of any columns in the target table or view that are populated by BEFORE INSERT triggers, such as primary keys and case-insensitive search columns. Those columns should be excluded from both the column_list and the VALUES list if, as they should, the triggers test the NEW.column_name for NULL.

6.2.1. `INSERT … VALUES`

The VALUES list must provide a value for every column in the column list, in the same order and of the correct type. The column list need not specify every column in the target but, if the column list is absent, the engine requires a value for every column in the table or view (computed columns excluded).

ⓘ

Note

Introducer syntax provides a way to identify the character set of a value that is a string constant (literal). Introducer syntax works only with literal strings: it cannot be applied to string variables, parameters, column references or values that are expressions.

Examples

  |INSERT INTO cars (make, model, year)
  |VALUES ('Ford', 'T', 1908);
  | 
  |INSERT INTO cars
  |VALUES ('Ford', 'T', 1908, 'USA', 850);
  | 
  |-- notice the '_' prefix (introducer syntax)
  |INSERT INTO People
  |VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

6.2.2. `INSERT … SELECT`

For this method of inserting, the output columns of the SELECT statement must provide a value for every target column in the column list, in the same order and of the correct type.

Literal values, context variables or expressions of compatible type can be substituted for any column in the source row. In this case, a source column list and a corresponding VALUES list are required.

If the column list is absent — as it is when SELECT * is used for the source expression — the column_list must contain the names of every column in the target table or view (computed columns excluded).

Examples

   |INSERT INTO cars (make, model, year)
   |  SELECT make, model, year
   |  FROM new_cars;
   | 
   |INSERT INTO cars
   |  SELECT * FROM new_cars;
   | 
   |INSERT INTO Members (number, name)
   |  SELECT number, name FROM NewMembers
   |    WHERE Accepted = 1
   |UNION ALL
   |  SELECT number, name FROM SuspendedMembers
   |    WHERE Vindicated = 1
   | 
   |INSERT INTO numbers(num)
   |  WITH RECURSIVE r(n) as (
   |    SELECT 1 FROM rdb$database
   |    UNION ALL
   |    SELECT n+1 FROM r WHERE n < 100
   |  )
   |SELECT n FROM r

Of course, the column names in the source table need not be the same as those in the target table. Any type of SELECT statement is permitted, as long as its output columns exactly match the insert columns in number, order and type. Types need not be exactly the same, but they must be assignment-compatible.

6.2.2.1. The Unstable Cursor Problem

In Firebird, up to and including Firebird 2.5, it is necessary to be aware of an implementation fault that affects this style of inserts when the objective is to duplicate rows in the same table. For example,

  |INSERT INTO T
  |  SELECT * FROM T;

known affectionately as the infinite insertion loop, will continuously select rows and insert them, over and over, until the system runs out of storage space.

This is a quirk that affects all data-changing DML operations, with a variety of effects. It happens because, in the execution layers, DML statements use implicit cursors for performing the operations. Thus, using our simple example, execution works as follows:

  |FOR SELECT <values> FROM T INTO <tmp_vars>
  |  DO
  |    INSERT INTO T VALUES (<tmp_vars>);

The implementation results in behaviour that does not accord with the SQL standards. Future versions of Firebird will comply with the standard.

6.2.3. `INSERT … DEFAULT VALUES`

The DEFAULT VALUES clause allows insertion of a record without providing any values at all, either directly or from a SELECT statement. This is only possible if every NOT NULL or CHECKed column in the table either has a valid default declared or gets such a value from a BEFORE INSERT trigger. Furthermore, triggers providing required field values must not depend on the presence of input values.

Example

  |INSERT INTO journal
  |  DEFAULT VALUES
  |RETURNING entry_id;

6.2.4. The `RETURNING` clause

An INSERT statement adding at most one row may optionally include a RETURNING clause in order to return values from the inserted row. The clause, if present, need not contain all of the insert columns and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE INSERT triggers.

☝

ALERT : Multiple INSERTs

In DSQL, a statement with RETURNING always returns only one row. If the RETURNING clause is specified and more than one row is inserted by the INSERT statement, the statement fails and an error message is returned. This behaviour may change in future Firebird versions.

Examples

   |INSERT INTO Scholars (
   |  firstname,
   |  lastname,
   |  address,
   |  phone,
   |  email)
   |VALUES (
   |  'Henry',
   |  'Higgins',
   |  '27A Wimpole Street',
   |  '3231212',
   |  NULL)
   |RETURNING lastname, fullname, id;
   | 
   |INSERT INTO Dumbbells (firstname, lastname, iq)
   |  SELECT fname, lname, iq
   |FROM Friends
   |  ORDER BY iq ROWS 1
   |  RETURNING id, firstname, iq
   |INTO :id, :fname, :iq;

Notes

RETURNING is only supported for VALUES inserts and singleton SELECT inserts.
In DSQL, a statement with a RETURNING clause always returns exactly one row. If no record was actually inserted, the fields in this row are all NULL. This behaviour may change in a later version of Firebird. In PSQL, if no row was inserted, nothing is returned, and the target variables keep their existing values.

6.2.5. Inserting into `BLOB` columns

Inserting into BLOB columns is only possible under the following circumstances:

The client application has made special provisions for such inserts, using the Firebird API. In this case, the modus operandi is application-specific and outside the scope of this manual.
The value inserted is a text string of no more than 32767 bytes.
⚠
Caution
If the value is not a string literal, beware of concatenations, as the output from the expression may exceed the maximum length.
You are using the INSERT … SELECT form and one or more columns in the result set are BLOBs.

Prev	Up	Next
Chapter 6. Data Manipulation (DML) Statements	Home	6.3. `UPDATE`