FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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.

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;

OVERRIDING

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');