INSERT keyword

INSERT ingests selected data into a database table.

Syntax#

Inserting values directly or using sub-queries:

Flow chart showing the syntax of the INSERT keyword

Inserting using sub-query alias:

Flow chart showing the syntax of the WITH AS INSERT keyword

Description#

Inserting values directly or using sub-queries:

  • VALUE: Directly defines the values to be inserted.
  • SELECT: Inserts values based on the result of a SELECT query

Setting sub-qeury alias:

  • WITH AS: Inserts values based on a subu-query, to which an alias is given by using WITH.

Parameter:

  • batch expects a batchCount (integer) value defining how many records to process at any one time.

Examples#

Inserting all columns
INSERT INTO trades
VALUES(
'2021-10-05T11:31:35.878Z',
'AAPL',
255,
123.33,
'B');
Bulk inserts
INSERT INTO trades
VALUES
('2021-10-05T11:31:35.878Z', 'AAPL', 245, 123.4, 'C'),
('2021-10-05T12:31:35.878Z', 'AAPL', 245, 123.3, 'C'),
('2021-10-05T13:31:35.878Z', 'AAPL', 250, 123.1, 'C'),
('2021-10-05T14:31:35.878Z', 'AAPL', 250, 123.0, 'C');
Specifying schema
INSERT INTO trades (timestamp, symbol, quantity, price, side)
VALUES(
to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
'AAPL',
255,
123.33,
'B');
note

Columns can be omitted during INSERT in which case the value will be NULL

Inserting only specific columns
INSERT INTO trades (timestamp, symbol, price)
VALUES(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),'AAPL','B');

Inserting query results#

This method allows you to insert as many rows as your query returns at once.

Insert as select
INSERT INTO confirmed_trades
SELECT timestamp, instrument, quantity, price, side
FROM unconfirmed_trades
WHERE trade_id = '47219345234';

Using the WITH keyword to set up an alias for a SELECT sub-query:

Insert with sub-query
WITH confirmed_id AS (
SELECT * FROM unconfirmed_trades
WHERE trade_id = '47219345234'
)
INSERT INTO confirmed_trades
SELECT * FROM confirmed_id;

Parameters for QuestDB 6.5.5 and earlier versions#

note

Deprecated content

This section applies to QuestDB 6.5.5 and earlier versions. From QuestDB 6.6 onwards, the database adjusts relevant settings automatically and provides maximum ingestion speed.

Inserting values directly or using sub-queries:

Flow chart showing the syntax of the INSERT keyword with commit lag settings

Inserting using sub-query alias:

Flow chart showing the syntax of the WITH AS INSERT keyword with commit lag settings

The commitLag parameter may be provided to optimize INSERT AS SELECT or WITH AS queries when inserting out-of-order records into an ordered dataset:

  • commitLag expects a lagAmount with a modifier to specify the time unit for the value (i.e. 20s for 20 seconds). The following table describes the units that may be used:

    unitdescription
    usmicroseconds
    sseconds
    mminutes
    hhours
    ddays
Insert as select with lag and batch size
INSERT batch 100000 commitLag 180s INTO trades
SELECT ts, instrument, quantity, price
FROM unordered_trades