INSERT
CrateDB defines the full INSERT
syntax as:
table_ident
The identifier (optionally schema-qualified) of an existing table.
column_ident
The name of a column or field in the table_ident
table.
expression
An expression or value to assign to the corresponding column.
query
A query (i.e., ) that supplies rows for the statement to insert.
output_expression
An expression to be computed and returned by the INSERT
statement after each row is updated. This expression can use any of the table column names, the *
character to return all table columns, as well as any system columns.
output_name
A name to use for the result of the output expression.
Description
The INSERT
creates one or more rows specified by value expressions.
You can list target column names in any order. If you omit the target column names, they default to all columns of the table or up to n columns if there are fewer values in the VALUES
clause or query
.
The values supplied by the VALUES
clause or query
are associated with the explicit or implicit column list left-to-right.
CrateDB will not fill any column not present in the explicit or implicit column list.
If the for any column is not of the correct data type, CrateDB will attempt automatic type conversion.
The optional RETURNING
clause causes the INSERT
statement to compute and return values from each row inserted (or updated, in the case of ON CONFLICT DO UPDATE
). You can take advantage of this behavior to obtain values that CrateDB supplied from defaults, such as as .
Caution
Dynamic SELECT statements may produce inconsistent values for insertion when used with the query
parameter.
For example, this use of produces a single column (foo
) with incompatible data types (numeric and , respectively):
SELECT unnest([{foo=1}, {foo='a string'}])
The same problem could happen like this:
INSERT INTO table_a (obj_col) VALUES ({foo=1}), ({foo='a string'})
INSERT INTO table_a (int_col) (SELECT obj_col['foo'] FROM table_a)
In this example, problems will arise if valid_col
is a valid column name, but invalid_col
is not:
SELECT unnest([{valid_col='foo', invalid_col='bar'}])
Any inserts that were successful before CrateDB encountered an error will remain, but CrateDB will reject the rest, potentially leading to inconsistent data.
Users need to take special care when inserting data from queries that might produce dynamic values like the ones above.
If your table has a primary key, you can use the ON CONFLICT DO UPDATE SET
clause to modify the existing record (instead of inserting a new one) if CrateDB encounters a primary key conflict during the INSERT
operation.
Syntax:
Where conflict_target
can be one or more column identifiers:
column_ident [, ... ]
And assignments
can be one or more column assignments:
assignments = expression [, ... ]
CrateDB does not support unique constraints, foreign key constraints, or exclusion constraints (see SQL compatibility: Unsupported features and functions). Therefore, the only constraint capable of producing a conflict that CrateDB supports is a constraint.
When using the ON CONFLICT DO UPDATE SET
clause with a primary key constraint, the conflict_target
must always match the primary key definition.
For example, if my_table
had a primary key col_a
, the correct syntax would be:
However, if my_table
had a primary key on both col_a
and col_b
, the correct syntax would be:
For example:
cr> INSERT INTO uservisits (id, name, visits, last_visit) VALUES
... (
... 0,
... 'Ford',
... 1,
... '2015-09-12'
... ) ON CONFLICT (id) DO UPDATE SET
... visits = visits + 1;
INSERT OK, 1 row affected (... sec)
This statement instructs CrateDB to do the following:
Attempt to insert a new
uservisits
record for user ID .If the insert would cause a primary key conflict on
id
(i.e., the user already has a record in theuservists
table), update the existing record by incrementing thevisits
count.
You can also use a virtual table named excluded
to reference values from the failed (i.e., excluded) INSERT
record. For example:
... (
... 0,
... 'Ford',
... 1,
... '2015-09-12'
... ) ON CONFLICT (id) DO UPDATE SET
... visits = visits + 1,
... last_visit = excluded.last_visit;
INSERT OK, 1 row affected (... sec)
The addition of last_visit = excluded.last_visit
instructs CrateDB to overwrite the existing value of last_visits
with the attempted insert value.
See also
If you use the ON CONFLICT DO NOTHING
clause, CrateDB will silently ignore rows that would cause a duplicate key conflict (i.e., CrateDB will not insert them and will not produce an error). For example:
INSERT INTO my_table (col_a, col_b) VALUES (1, 42)
ON CONFLICT DO NOTHING
In the statement above, if col_a
had a primary key constraint and the value 1
already existed for col_a
, CrateDB would not perform an insert.
Note