INSERT
NAME
INSERT: insert rows
SYNOPSIS
<insert statement> ::=
INSERT [ WITH <XML lexically scoped options> ] INTO <table name> <insert columns and source>
<insert columns and source> ::=
[ ( <column name> [ { , <column name> }... ] ) ] [ <override clause> ] <query expression>
| DEFAULT VALUES
<override clause> ::= OVERRIDING USER VALUE | OVERRIDING SYSTEM VALUE
<row value constructor element> ::= <value expression> | NULL | DEFAULT
The <query expression> is any query (see SELECT); most
often VALUES (...), (...), each row a list of
<row value constructor element>s.
DESCRIPTION
INSERT adds rows to a base table, or through an updatable view to the table it is drawn from. The rows are those of its query, each value going to a column of the column list, in order, or, without one, to each column of the table in order. A column not given a value takes its default: the column's, its domain's, or the null value.
In VALUES, DEFAULT gives a column its default and NULL the null value. DEFAULT VALUES inserts one row of every column's default.
An identity column given no value, or DEFAULT, takes its generator's next value, one for each row. One GENERATED ALWAYS is given a value of the query's only with OVERRIDING SYSTEM VALUE. OVERRIDING USER VALUE ignores the values given it, and each row takes the generator's next value. A generated column is given only DEFAULT, or no value. Its value is computed from the row once the other columns have theirs, an identity column's among them. The columns of a table's system-time period take the transaction timestamp and the highest value of their type, whatever is given them.
OVERRIDING and SYSTEM are non-reserved words.
The query is evaluated in full before any row is inserted, so a query that reads the table inserted into sees none of the rows it inserts. An INSERT whose query gives no row completes with no data.
The table's triggers run as CREATE TRIGGER says: its BEFORE triggers once the rows are given their values, before any is inserted, after which a generated column is computed again from each row as they left it, its AFTER triggers at the end of the statement. A view with an INSTEAD OF trigger of the statement's event runs it in place of the change, and nothing is written through the view: its row count is of the rows the trigger was given. A column of the view not given a value, or given DEFAULT, takes the default of the column of a base table it is drawn from by column references alone, and is null if it has no such column or that column is an identity or generated one. An updatable view over such a view runs its trigger the same way.
A query reads the rows the statement changed as a table, written OLD TABLE, NEW TABLE or FINAL TABLE around the statement (Data Change Delta Tables).
The query may read another statement's rows the same way, as the one table reference of its FROM clause, and that statement runs first.
RULES
- The table exists and is not read-only: a base table, or a view that can be updated.
- Each column listed is a column of it, listed once.
- The query has as many columns as are inserted into, each value assignable to its column's type.
- Each row inserted satisfies the table's constraints, and, through a view WITH CHECK OPTION, is a row of the view.
- The transaction is not READ ONLY, but for a temporary table.
- A generated column is given only DEFAULT, in every row, or is not inserted into.
- A value given an identity column GENERATED ALWAYS needs an override clause. One GENERATED BY DEFAULT takes only OVERRIDING USER VALUE. An override clause is written only of an identity column given values.
- A value given a column of ROW START or ROW END needs OVERRIDING USER VALUE, which ignores it, and no other override clause.
PRIVILEGES
INSERT on each column inserted into: each listed, or, without a list, each of the table's; INSERT granted on the table is on each of its columns. SELECT on each table the query reads.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -103 | The table does not exist or is read-only; a column listed is not of it, or is listed twice; the query's columns are not as many as those inserted into, or a value is not assignable to its column; WITH's XML options break a rule of XML Values |
| 2A000 | -104 | Its user lacks a privilege it needs |
| 23000 | -201 | A row breaks a constraint: NOT NULL, UNIQUE, PRIMARY KEY, a foreign key or a CHECK |
| 44000 | -201 | A row inserted through a view WITH CHECK OPTION is not a row of the view |
| 22xxx | -301 | A data exception as a value is computed or assigned |
| 25000 | -917 | The transaction is READ ONLY |
| 02000 | 100 | Not an error: the query gave no row |
A statement that fails inserts no row.
EXAMPLES
CREATE TABLE PARTS (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20) DEFAULT 'unnamed', QTY INTEGER DEFAULT 0);
INSERT INTO PARTS VALUES (1, 'bolt', 100);
INSERT INTO PARTS (ID, NAME) VALUES (2, 'nut'), (3, DEFAULT);
INSERT INTO PARTS (ID) SELECT ID + 10 FROM PARTS;
SELECT * FROM PARTS ORDER BY ID;
OK
OK
OK
OK
ID | NAME | QTY
--------------+----------------------+--------------
1 | bolt | 100
2 | nut | 0
3 | unnamed | 0
11 | unnamed | 0
12 | unnamed | 0
13 | unnamed | 0
(6 rows)
The INSERT ... SELECT read the three rows there before it inserted its own, so it inserted three.
Rows refused:
INSERT INTO PARTS VALUES (1, 'again', 1);
INSERT INTO PARTS (NAME) VALUES ('no id');
INSERT INTO PARTS VALUES (5, 'big', 'many');
turborel: SQLCODE -201 (SQLSTATE 23000): PRIMARY KEY ANN.PARTS_PRIMARY1 (ID) of ANN.PARTS would be violated: a row of these values is there [X3.135-1986 §6.4 GR2]
turborel: SQLCODE -201 (SQLSTATE 23000): column ID of ANN.PARTS is NOT NULL [X3.135-1986 §6.3 SR3, GR1]
turborel: SQLCODE -103 (SQLSTATE 2A000): cannot assign a CHARACTER(4) value to column QTY (INTEGER) [ISO/IEC 9075:1992 §13.8 SR6; ISO/IEC 9075:1992 §9.2 SR2] (at character 36)
Into an identity column and a generated column:
CREATE TABLE TICKETS (TICKET INTEGER GENERATED ALWAYS AS IDENTITY, SEAT CHAR(3), ROW_NO GENERATED ALWAYS AS (SUBSTRING(SEAT FROM 1 FOR 1)));
INSERT INTO TICKETS (SEAT) VALUES ('A12'), ('B07');
INSERT INTO TICKETS VALUES (DEFAULT, 'C01', DEFAULT);
INSERT INTO TICKETS VALUES (50, 'D02', DEFAULT);
INSERT INTO TICKETS OVERRIDING SYSTEM VALUE VALUES (50, 'D02', DEFAULT);
INSERT INTO TICKETS (SEAT, ROW_NO) VALUES ('E05', 'E');
SELECT * FROM TICKETS ORDER BY TICKET;
OK
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): identity column TICKET is GENERATED ALWAYS, so a value is given it only with OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE [ISO/IEC 9075-2:2023 §14.11 SR13b] (at character 28)
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): column ROW_NO is a generated column, so it is given only DEFAULT [ISO/IEC 9075-2:2023 §14.11 SR12] (at character 50)
TICKET | SEAT | ROW_NO
--------------+------+--------
1 | A12 | A
2 | B07 | B
3 | C01 | C
50 | D02 | D
(4 rows)
Through a view WITH CHECK OPTION:
CREATE VIEW SMALL AS SELECT ID, NAME FROM PARTS WHERE ID < 100 WITH CHECK OPTION;
INSERT INTO SMALL VALUES (500, 'too big');
INSERT INTO SMALL VALUES (50, 'fits');
SELECT ID, NAME, QTY FROM PARTS WHERE ID = 50;
OK
turborel: SQLCODE -201 (SQLSTATE 44000): the row is not in view ANN.SMALL, defined WITH CASCADED CHECK OPTION [ISO/IEC 9075:1992 §11.19 GR11; X3.135-1986 §8.7 GR2]
OK
ID | NAME | QTY
--------------+----------------------+--------------
50 | fits | 0
(1 row)
STANDARD
ISO/IEC 9075:1992 §13.8, and X3.135-1986 §8.7, whose INSERT takes
VALUES of one row or a query specification. DEFAULT VALUES, a
<query expression> of any kind, and NULL and DEFAULT in VALUES are
SQL-92's [§13.8, §7.1]. The override clause, and the values identity
columns and generated columns take, are ISO/IEC 9075-2:2023 §14.11 and
§15.11. WITH's XML options are ISO/IEC 9075-14:2006 draft §14.4,
Features X082 and X132.
SEE ALSO
UPDATE, DELETE, SELECT, CREATE TABLE, CREATE TRIGGER, Changing Data, Data Change Delta Tables