INSERT
¶
You can use the INSERT
statement to insert
new rows into a table.
Table of contents
Synopsis¶
CrateDB defines the full INSERT
syntax as:
INSERT INTO table_ident
[ ( column_ident [, ...] ) ]
{ VALUES ( expression [, ...] ) [, ...] | ( query ) | query }
[ ON CONFLICT (column_ident [, ...]) DO UPDATE SET { column_ident = expression [, ...] } |
ON CONFLICT [ ( column_ident [, ...] ) ] DO NOTHING ]
[ RETURNING { * | output_expression [ [ AS ] output_name ] | relation.* } [, ...] ]
Parameters¶
- 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., SELECT) 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
statement 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
.
CrateDB will order implicitly inferred column names by their ordinal value. The ordinal value depends on the ordering of the columns within the CREATE TABLE statement.
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 values for any column are not of the correct data type, CrateDB will attempt automatic type conversion.
Note
When inserting data from a query, the number of rows affected indicates
the number of rows for which the INSERT
succeeded.
Please refer to Data manipulation for more details.
Inserting data from a query uses Overload Protection to ensure other queries can still perform. Please change these settings during large inserts if needed.
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 _id.
Caution
Dynamic SELECT statements may produce inconsistent
values for insertion when used with the query
parameter.
For example, this use of unnest produces a single column (foo
) with
incompatible data types (numeric and
character, 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.
ON CONFLICT DO UPDATE SET
¶
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:
ON CONFLICT (conflict_target) DO UPDATE SET { assignments }
Where conflict_target
can be one or more column identifiers:
column_ident [, ... ]
And assignments
can be one or more column assignments:
assignments = expression [, ... ]
Note
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 primary key 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:
ON CONFLICT (col_a) DO UPDATE SET { assignments }
However, if my_table
had a primary key on both col_a
and col_b
,
the correct syntax would be:
ON CONFLICT (col_a, col_b) DO UPDATE SET { assignments }
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 ID0
.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:
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,
... 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
ON CONFLICT DO NOTHING
¶
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
You may specify an explicit primary key as the conflict_target
(i.e.,
ON CONFLICT (conflict_target) DO NOTHING
), as with ON CONFLICT DO
UPDATE SET. However, doing so is
optional.