Reference and syntax for the INSERT command.
Inserts one or more values into a specified table. Specifying column names is optional.
Parameter | Description |
---|---|
<table> | The table to insert into. |
( <column_name> [ , ... ] ) | List of column names to be inserted into. If not defined, the columns will be deduced from the expression. |
<expression> —OR— VALUES ( <value> [ , ... ] ) [, ... ] | Either a SELECT query or an explicit list of VALUES tuples to be inserted. |
<settings> | List of query-specific settings overrides. |
This feature is in public preview.
The optional ON CONFLICT
clause provides a way to reconcile or merge new data with existing records by either skipping or overwriting rows that already exist. It is available for a narrow subset of INSERT
statements. For more generic UPSERT or deduplication functionality, use the MERGE
statement.
Given this clause, for each row proposed for insertion, either the insertion proceeds, or, if there already exists a row in the table matching the tuple specified by the CONFLICT
columns, an alternative action will be taken. There are two possible alternatives - take no action, or update the pre-existing matched row(s) in some way.
DO NOTHING
enables an INSERT IGNORE
workload, whereby the newer row is simply discarded.DO UPDATE
enables an UPSERT or INSERT UPDATE
workload, whereby the pre-existing matched row(s) can be partially or fully overwritten. The overwrite expressions can (but do not have to) reference the fresh data using the EXCLUDED
view name.Parameter | Description |
---|---|
( <conflict_column_name> [ , ... ] ) | List of column names to determine CONFLICT duplicates |
<conflict_action> | Action to take when a conflict occurs: DO NOTHING to skip the conflicting row, or DO UPDATE SET to update the existing matched row(s). |
INSERT
expression must be a single VALUES
tuple.CONFLICT
column must be specified. These are not automatically deduced from primary index, etc.INSERT
column list must be specified. Their names and ordering cannot get deduced from VALUES
.First, create a table populated with student information as follows:
Next, use INSERT
to add two rows into the students
table as follows:
You can also add another row with only some of the columns populated. The missing dob
column for date of birth does not have default value, so Firebolt sets it to NULL
.
Handle duplicate key conflicts by doing nothing:
Handle duplicate key conflicts by updating existing rows:
Apply arbitrary transformations while updating matching rows:
insert_sharding
to enforce partition locality during ingestion into partitioned tables.tablet_min_size_bytes
and tablet_max_size_bytes
to control min/max tablet sizes during ingestion.max_insert_threads
to control the maximum number of threads for INSERT
statements, limiting the degree of parallelism for tablet writing operations. This can reduce memory footprint during ingestion.