Edit this page

INSERT

The INSERT statement inserts new data into a table.

Examples

Insert the values 1, 2, 3 into tbl:

Query

INSERT INTO tbl    VALUES (1), (2), (3);

Insert the result of a query into a table:

Query

INSERT INTO tbl    SELECT * FROM other_tbl;

Insert values into the i column, inserting the default value into other columns:

Query

INSERT INTO tbl (i)    VALUES (1), (2), (3);

Explicitly insert the default value into a column:

Query

INSERT INTO tbl (i)    VALUES (1), (DEFAULT), (3);

Assuming tbl has a primary key/unique constraint, do nothing on conflict:

Query

INSERT OR IGNORE INTO tbl (i)    VALUES (1);

Or update the table with the new values instead:

Query

INSERT OR REPLACE INTO tbl (i)    VALUES (1);

Syntax

INSERT INTO inserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query.

Insert Column Order

It's possible to provide an optional insert column order, this can either be BY POSITION (the default) or BY NAME. Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or NULL if there is none.

If the expression for any column is not of the correct data type, automatic type conversion will be attempted.

`INSERT INTO ... [BY POSITION]`

The order that values are inserted into the columns of the table is determined by the order that the columns were declared in. That is, the values supplied by the VALUES clause or query are associated with the column list left-to-right. This is the default option, that can be explicitly specified using the BY POSITION option. For example:

Query

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl    VALUES (5, 42);

Specifying BY POSITION is optional and is equivalent to the default behavior:

Query

INSERT INTO tbl    BY POSITION    VALUES (5, 42);

To use a different order, column names can be provided as part of the target, for example:

Query

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl (b, a)    VALUES (5, 42);

Adding BY POSITION results in the same behavior:

Query

INSERT INTO tbl    BY POSITION (b, a)    VALUES (5, 42);

This will insert 5 into b and 42 into a.

`INSERT INTO ... BY NAME`

Using the BY NAME modifier, the names of the column list of the SELECT statement are matched against the column names of the table to determine the order that values should be inserted into the table. This allows inserting even in cases when the order of the columns in the table differs from the order of the values in the SELECT statement or certain columns are missing.

For example:

Query

CREATE TABLE tbl (a INTEGER, b INTEGER);
INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a);
INSERT INTO tbl BY NAME (SELECT 22 AS b);
SELECT * FROM tbl;

Result

 a    | b------+----   32 | 42 NULL | 22

It's important to note that when using INSERT INTO ... BY NAME, the column names specified in the SELECT statement must match the column names in the table. If a column name is misspelled or does not exist in the table, an error will occur. Columns that are missing from the SELECT statement will be filled with the default value.

`ON CONFLICT` Clause

An ON CONFLICT clause can be used to perform a certain action on conflicts that arise from UNIQUE or PRIMARY KEY constraints. An example for such a conflict is shown in the following example:

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl    VALUES (1, 42);
INSERT INTO tbl    VALUES (1, 84);

Result

db error: ERROR: Duplicate key "i: 1" violates primary key constraint.

This raises a constraint error. The table will contain the row that was first inserted:

Query

SELECT * FROM tbl;

Result

 i | j---+---- 1 | 42

These error messages can be avoided by explicitly handling conflicts. SereneDB supports two such clauses: ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE SET ....

`DO NOTHING` Clause

The DO NOTHING clause causes the error(s) to be ignored, and the values are not inserted or updated. For example:

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl    VALUES (1, 42);
INSERT INTO tbl    VALUES (1, 84)    ON CONFLICT DO NOTHING;

These statements finish successfully and leave the table with the row <i: 1, j: 42>.

`INSERT OR IGNORE INTO`

The INSERT OR IGNORE INTO ... statement is a shorter syntax alternative to INSERT INTO ... ON CONFLICT DO NOTHING. For example, the following statements are equivalent:

Query

INSERT OR IGNORE INTO tbl    VALUES (1, 84);
INSERT INTO tbl    VALUES (1, 84) ON CONFLICT DO NOTHING;

`DO UPDATE` Clause (Upsert)

The DO UPDATE clause causes the INSERT to turn into an UPDATE on the conflicting row(s) instead. The SET expressions that follow determine how these rows are updated. The expressions can use the special virtual table EXCLUDED, which contains the conflicting values for the row. Optionally you can provide an additional WHERE clause that can exclude certain rows from the update. The conflicts that don't meet this condition are ignored instead.

Because we need a way to refer to both the to-be-inserted tuple and the existing tuple, we introduce the special EXCLUDED qualifier. When the EXCLUDED qualifier is provided, the reference refers to the to-be-inserted tuple, otherwise, it refers to the existing tuple. This special qualifier can be used within the WHERE clauses and SET expressions of the ON CONFLICT clause.

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl VALUES (1, 42);
INSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Examples

An example using DO UPDATE is the following:

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl    VALUES (1, 42);
INSERT INTO tbl    VALUES (1, 84)    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

Result

 i | j---+---- 1 | 84

Rearranging columns and using BY NAME is also possible:

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl    VALUES (1, 42);
INSERT INTO tbl (j, i)    VALUES (168, 1)    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl    BY NAME (SELECT 1 AS i, 336 AS j)    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
SELECT * FROM tbl;

Result

 i | j---+----- 1 | 336

`INSERT OR REPLACE INTO`

The INSERT OR REPLACE INTO ... statement is a shorter syntax alternative to INSERT INTO ... DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2, .... That is, it updates every column of the existing row to the new values of the to-be-inserted row. For example, given the following input table:

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER);
INSERT INTO tbl    VALUES (1, 42);

These statements are equivalent:

Query

INSERT OR REPLACE INTO tbl    VALUES (1, 84);
INSERT INTO tbl    VALUES (1, 84)    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl (j, i)    VALUES (84, 1)    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;
INSERT INTO tbl BY NAME    (SELECT 84 AS j, 1 AS i)    ON CONFLICT DO UPDATE SET j = EXCLUDED.j;

Limitations

When the ON CONFLICT ... DO UPDATE clause is used and a conflict occurs, only the columns named in the SET clause are updated. Columns that are unaffected by the conflict keep their existing values, so their NOT NULL constraints continue to hold. For example, the following upsert updates val1 while preserving the existing val2:

Query

CREATE TABLE t1 (id INTEGER PRIMARY KEY, val1 DOUBLE, val2 DOUBLE NOT NULL);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, val1 DOUBLE);
INSERT INTO t1    VALUES (1, 2, 3);
INSERT INTO t2    VALUES (1, 5);
INSERT INTO t1 BY NAME (SELECT id, val1 FROM t2)    ON CONFLICT DO UPDATE    SET val1 = EXCLUDED.val1;

Composite Primary Key

When multiple columns need to be part of the uniqueness constraint, use a single PRIMARY KEY clause including all relevant columns:

Query

CREATE TABLE t1 (id1 INTEGER, id2 INTEGER, val1 DOUBLE, PRIMARY KEY (id1, id2));
INSERT OR REPLACE INTO t1    VALUES (1, 2, 3);
INSERT OR REPLACE INTO t1    VALUES (1, 2, 4);

Defining a Conflict Target

A conflict target may be provided as ON CONFLICT (conflict_target). This is a group of columns that an index or uniqueness/key constraint is defined on. If the conflict target is omitted, the PRIMARY KEY constraint(s) on the table are targeted.

Specifying a conflict target is optional unless using a DO UPDATE and there are multiple unique/primary key constraints on the table.

Query

CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl    VALUES (1, 20, 300);
SELECT * FROM tbl;

Result

 i | j  | k---+----+----- 1 | 20 | 300

Targeting the PRIMARY KEY column i resolves the conflict and updates the row:

Query

INSERT INTO tbl    VALUES (1, 40, 700)    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k;
SELECT i, j, k FROM tbl ORDER BY i;

Result

 i | j  | k---+----+------ 1 | 20 | 1400

The conflict target can also be backed by a UNIQUE constraint. Targeting the UNIQUE column j likewise resolves the conflict and updates the row:

Query

INSERT INTO tbl    VALUES (1, 20, 900)    ON CONFLICT (j) DO UPDATE SET k = 5 * EXCLUDED.k;

When a conflict target is provided, you can further filter this with a WHERE clause, that should be met by all conflicts.

Query

INSERT INTO tbl    VALUES (1, 40, 700)    ON CONFLICT (i) DO UPDATE SET k = 2 * EXCLUDED.k WHERE k < 100;

`RETURNING` Clause

The RETURNING clause may be used to return the contents of the rows that were inserted. This can be useful if some columns are calculated upon insert. For example, if the table contains an automatically incrementing primary key, then the RETURNING clause will include the automatically created primary key. This is also useful in the case of generated columns.

Some or all columns can be explicitly chosen to be returned and they may optionally be renamed using aliases. Arbitrary non-aggregating expressions may also be returned instead of simply returning a column. All columns can be returned using the * expression, and columns or expressions can be returned in addition to all columns returned by the *.

For example:

Query

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1    SELECT 42    RETURNING *;

Result

 i---- 42

A more complex example that includes an expression in the RETURNING clause:

Query

CREATE TABLE t2 (i INTEGER, j INTEGER);
INSERT INTO t2    SELECT 2 AS i, 3 AS j    RETURNING *, i * j AS i_times_j;

Result

 i | j | i_times_j---+---+----------- 2 | 3 |         6

The next example shows a situation where the RETURNING clause is more helpful. First, a table is created with a primary key column. Then a sequence is created to allow for that primary key to be incremented as new rows are inserted. When we insert into the table, we do not already know the values generated by the sequence, so it is valuable to return them. For additional information, see the CREATE SEQUENCE page.

Query

CREATE TABLE t3 (i INTEGER PRIMARY KEY, j INTEGER);
CREATE SEQUENCE 't3_key';
INSERT INTO t3    SELECT nextval('t3_key') AS i, 42 AS j    UNION ALL    SELECT nextval('t3_key') AS i, 43 AS j    RETURNING *;

Result

 i | j---+---- 1 | 42 2 | 43

Examples​

Syntax​

Insert Column Order​

INSERT INTO ... [BY POSITION]​

INSERT INTO ... BY NAME​

ON CONFLICT Clause​

DO NOTHING Clause​

INSERT OR IGNORE INTO​

DO UPDATE Clause (Upsert)​

Examples​

INSERT OR REPLACE INTO​

Limitations​

Composite Primary Key​

Defining a Conflict Target​

RETURNING Clause​

Examples

Syntax

Insert Column Order

`INSERT INTO ... [BY POSITION]`

`INSERT INTO ... BY NAME`

`ON CONFLICT` Clause

`DO NOTHING` Clause

`INSERT OR IGNORE INTO`

`DO UPDATE` Clause (Upsert)

Examples

`INSERT OR REPLACE INTO`

Limitations

Composite Primary Key

Defining a Conflict Target

`RETURNING` Clause