INSERT

Inserts one or more values into a specified table. Specifying column names is optional, and source values map to target columns either by position (the default) or by name.

Syntax

INSERT INTO <table>
  [ { BY NAME | BY POSITION } ]
  [ ( <column_name> [ , ... ] ) ]
  { <expression> | VALUES ( <value> [ , ... ] ) [ , ... ] }
  [ WITH <settings> ]

Parameters

Parameter	Description
`<table>`	The table to insert into.
`{ BY NAME \| BY POSITION }`	How source values map to target columns. `BY POSITION` (the default) maps them in order. `BY NAME` matches each source column to the target column with the same name. `BY NAME` cannot be combined with a `( <column_name> [ , ... ] )` list. See Match columns by name.
`( <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.

Match columns by name

By default (BY POSITION), Firebolt maps source values to target columns in order: the first value goes to the first column, and so on. BY NAME instead matches each source column to the target column with the same name, so source order does not matter. BY NAME reads column names from the source, so it requires a SELECT source (not VALUES) and every source column must be named. It cannot be combined with an explicit ( <column_name> [ , ... ] ) list, since the target columns are derived from the source names. A source column whose name matches no target column raises an error. Target columns the source does not produce are filled the same way as a positional insert: with the column default, or NULL when the column is nullable and has no default.

INSERT INTO students BY NAME
SELECT 'Luna Lovegood' AS full_name, 4 AS id, 'F' AS gender;

This matches id, full_name, and gender by name regardless of their order in the SELECT, and leaves dob as NULL.

INSERT ON CONFLICT

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.
- DO UPDATE SET * can replace the exhaustive SET colA = EXCLUDED.colA, colB = EXCLUDED.colB, ... , colN = EXCLUDED.colN if no data transformation is required.

INSERT INTO <table> ( <column_name> [ , ... ] )
VALUES ( <value> [ , ... ] )
ON CONFLICT ( <conflict_column_name> [ , ... ] ) <conflict_action>
[ WITH <settings> ]

<conflict_action> ::= {
  DO NOTHING |
  DO UPDATE SET { * | { <column_name> = <value> [ , ... ] } }
}

Extra Parameters

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 ON CONFLICT Limitations:

INSERT expression must be a single VALUES tuple.
At least one CONFLICT column must be specified. These are not automatically deduced from primary index, etc.
Unless using INSERT *, the INSERT column list must be specified. Their names and ordering cannot get deduced from VALUES.

Examples

First, create a table populated with student information as follows:

CREATE TABLE students (
    id INT,
    full_name TEXT,
    dob DATE,
    gender TEXT)

Next, use INSERT to add two rows into the students table as follows:

INSERT INTO students VALUES
    (1, 'Harry Potter', DATE '1980-07-31', 'M'),
    (2, 'Hermione Granger', DATE '1979-09-19', 'F')

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.

INSERT INTO students (id, full_name, gender) VALUES (3, 'Ron Weasley', 'M')

Handle duplicate key conflicts by doing nothing:

INSERT INTO students (id, full_name, gender)
VALUES (1, 'Harry Potter', 'M')
ON CONFLICT (id) DO NOTHING;

Handle duplicate key conflicts by updating existing rows:

INSERT INTO students (id, full_name, gender)
VALUES (1, 'Harry James Potter', 'M')
ON CONFLICT (id) DO UPDATE SET
    full_name = EXCLUDED.full_name,
    gender = EXCLUDED.gender;

Apply arbitrary transformations while updating matching rows:

INSERT INTO hogsmeade_guest_log (guest_id, guest_name)
VALUES (1, 'Harry James Potter')
ON CONFLICT (guest_id) DO UPDATE SET
    guest_name = EXCLUDED.guest_name,
    last_visit_time = NOW(),  -- Update last_visit_time to current time
    visit_count = hogsmeade_guest_log.visit_count + 1;  -- Increment visit_count

Settings to control behavior

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.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Match columns by name

INSERT ON CONFLICT

Extra Parameters

INSERT ON CONFLICT Limitations:

Examples

Settings to control behavior

​Syntax

​Parameters

​Match columns by name

​INSERT ON CONFLICT

​Extra Parameters

​INSERT ON CONFLICT Limitations:

​Examples

​Settings to control behavior

Syntax

Parameters

Match columns by name

INSERT ON CONFLICT

Extra Parameters

INSERT ON CONFLICT Limitations:

Examples

Settings to control behavior