Reference and syntax for the INSERT command.
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. |
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
.INSERT
to add two rows into the students
table as follows:
dob
column for date of birth does not have default value, so Firebolt sets it to NULL
.
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.