6.2INSERT

Inserts rows of data into a table or updatable view

Syntax

   |INSERT INTO target
   |  { DEFAULT VALUES
   |  | [(<column_list>)] [<override_opt>] <value-source> }
   |  [RETURNING <returning_list> [INTO <variables>]]
   | 
   |<column_list> ::= col_name [, col_name ...]
   | 
   |<override_opt> ::=
   |  OVERRIDING {USER | SYSTEM} VALUE
   | 
   |<value-source> ::= VALUES (<value-list>) | <query-expression>
   | 
   |<value-list> ::= <ins-value> [, <ins-value> ...]
   | 
   |<ins-value> :: = <value-expression> | DEFAULT
   | 
   |<returning_list> ::= * | <output_column> [, <output_column]
   | 
   |<output_column> ::=
   |    target.*
   |  | <return_expression> [COLLATE collation] [[AS] alias]
   | 
   |<return_expression> ::=
   |    <value-expression>
   |  | [target.]col_name
   | 
   |<value-expression> ::=
   |    <literal>
   |  | <context-variable>
   |  | any other expression returning a single
   |    value of a Firebird data type or NULL
   | 
   |<variables> ::= [:]varname [, [:]varname ...]

Table 6.15Arguments for the INSERT Statement Parameters
ArgumentDescription

target

The name of the table or view to which a new row, or batch of rows, should be added

col_name

Name of a table or view column

value-expression

An expression whose value is used for inserting into the table or for returning

return_expression

The expression to be returned in the RETURNING clause

literal

A literal

context-variable

Context variable

varname

Name of a PSQL local variable

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

Restrictions
  • Columns returned to the NEW.column_name context variables in DML triggers should not have a colon (:) prefixed to their names

  • Columns may not 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.1INSERT …​ 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).

The expression DEFAULT allows a column to be specified in the column list, but instructs Firebird to use the default value (either NULL or the value specified in the DEFAULT clause of the column definition). For identity columns, specifying DEFAULT will generate the identity value. It is possible to include calculated columns in the column list and specifying DEFAULT as the column value.

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.2INSERT …​ SELECT

For this method of inserting, the output columns of the SELECT statement (or <query-expression>) 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 the same, but they must be assignment-compatible.

Since Firebird 5.0, an INSERT …​ SELECT with a RETURNING clause produces zero or more rows, and the statement is described as type isc_info_sql_stmt_select. In other words, an INSERT …​ SELECT …​ RETURNING will no longer produce a multiple rows in singleton select error when the select produces multiple rows.

For the time being, a INSERT …​ VALUES (…​) or INSERT …​ DEFAULT VALUES with a RETURNING clause is still described as isc_info_sql_stmt_exec_procedure. This behaviour may change in a future Firebird version.

6.2.3INSERT …​ 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.

Specifying DEFAULT VALUES is equivalent to specifying a values list with expression DEFAULT for all columns.

Example

  |INSERT INTO journal
  |  DEFAULT VALUES
  |RETURNING entry_id;

6.2.4OVERRIDING

The OVERRIDING clause controls the behaviour of an identity column for this statement only.

OVERRIDING SYSTEM VALUE

The user-provided value for the identity column is used, and no value is generated using the identity. In other words, for this insert, the identity will behave as if it is GENERATED BY DEFAULT. This option can only be specified for tables with a GENERATED ALWAYS identity column.

This can be useful when merging or importing data from another source. After such an insert, it may be necessary to change the next value of the identity sequence using ALTER TABLE to prevent subsequent inserts from generating colliding identity values.

OVERRIDING USER VALUE

The user-provided value for the identity column is ignored, and the column value is generated using the identity. In other words, for this insert, the identity will behave as if it is GENERATED ALWAYS, while allowing the identity column in the column-list. This option can be specified for both types of identity columns.

It is usually simpler to leave out the identity column to achieve the same effect.

Examples of OVERRIDING

  |-- for ALWAYS
  |-- value 11 is used anyway
  |insert into objects_always (id, name)
  |  OVERRIDING SYSTEM VALUE values (11, 'Laptop');
  | 
  |-- for both ALWAYS and BY DEFAULT
  |-- value 12 is not used
  |insert into objects_default (id, name)
  |  OVERRIDING USER VALUE values (12, 'Laptop');

6.2.5The RETURNING Clause

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

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using * or table_name.*.

The optional INTO sub-clause is only valid in PSQL.

Caveats for updatable views

The values reported by RETURNING for updatable views may be incorrect if the view is made updatable through triggers. See The RETURNING clause and updatable views.

Multiple INSERTs

In DSQL, an INSERT …​ VALUES (…​) RETURNING or INSERT …​ DEFAULT VALUES RETURNING returns only one row, and a INSERT …​ SELECT …​ RETURNING can return zero or more rows.

In PSQL, if the RETURNING clause is specified and more than one row is inserted by the INSERT statement, the statement fails and a multiple rows in singleton select error 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 Scholars (firstname, lastname, address,
   |  phone, email)
   |VALUES (
   |  'Henry', 'Higgins', '27A Wimpole Street',
   |  '3231212', NULL)
   |RETURNING *;
   | 
   |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;

  • In DSQL, an INSERT …​ VALUES (…​) RETURNING always returns exactly one row. This behaviour may change in a future Firebird version.

  • In DSQL, an INSERT …​ DEFAULT VALUES RETURNING always returns exactly one row.

  • In DSQL, an INSERT …​ SELECT …​ RETURNING returns zero or more rows.

  • In PSQL, if multiple rows are returned, the statement fails with a multiple rows in singleton select error. This behaviour may change in a future Firebird version.

  • In PSQL, if no row was inserted, nothing is returned, and the target variables keep their existing values.

6.2.6Inserting into BLOB columns

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

  1. 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.

  2. The value inserted is a string literal of no more than 65,533 bytes (64KB - 3).

    A limit, in characters, is calculated at run-time for strings that are in multibyte character sets, to avoid overrunning the bytes limit. For example, for a UTF8 string (max. 4 bytes/character), the run-time limit is likely to be about (floor(65533/4)) = 16383 characters.

  3. You are using the INSERT …​ SELECT form and one or more columns in the result set are BLOBs.