INSERT

Inserts one or more values into a specified table. Specifying column names is optional.

Syntax

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

Parameters

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.

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.

What is Firebolt?

Overview

Self-Managed

Performance and Observability

Guides

SQL reference

General reference

API reference

Legal

Syntax

Parameters

INSERT ON CONFLICT

Extra Parameters

INSERT ON CONFLICT Limitations:

Examples

Settings to control behavior

​Syntax

​Parameters

​INSERT ON CONFLICT

​Extra Parameters

​INSERT ON CONFLICT Limitations:

​Examples

​Settings to control behavior

Syntax

Parameters

INSERT ON CONFLICT

Extra Parameters

INSERT ON CONFLICT Limitations:

Examples

Settings to control behavior