INSERT

Add rows with INSERT, decide what happens with duplicate keys, get new ids back with RETURNING, and write times correctly.

• Category: Writing data
• Keywords: insert, add rows, new row, VALUES, INTO, column list, multi-row insert, upsert, INSERT OR REPLACE, REPLACE, INSERT OR IGNORE, IGNORE, ON CONFLICT, duplicate key, already exists, dedup, RETURNING, returning id, new id, last insert id, time zone, +05:45, Nepal time, UTC, INSERT SELECT, copy rows
• See also: UPDATE and DELETE, Loading many rows, Constraints, Column types, CREATE TABLE, Transactions, Time and time zones

INSERT adds rows to a table. One INSERT can add many rows, and it is all or nothing: when one row is bad, no row of that statement is saved. When KDB says inserted, the rows are already on the disk.

A real example: today's market candles arrive; one of them was already loaded.

Example: Load daily candles; a repeated row is replaced, not doubled

Setup:

CREATE TABLE ochl_day (time TIMESTAMP ROUND 1d, symbol SYMBOL, open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE, volume DOUBLE)
  TIMESERIES(time) PARTITION BY YEAR DEDUP KEY (symbol, time);
INSERT INTO ochl_day VALUES ('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 508.2, 91000);

INSERT OR REPLACE INTO ochl_day (time, symbol, open, high, low, close, volume) VALUES
  ('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 507.6, 91000),
  ('2026-09-13', 'COAST',  376.0, 379.9, 374.0, 378.5, 120500),
  ('2026-09-13', 'VOLT', 191.0, 194.2, 190.1, 193.7, 64000);
SELECT time, symbol, close FROM ochl_day ORDER BY symbol;

Result:

INSERT OR REPLACE INTO ochl_day (time, symbol, open, high, low, close, volume) VALUES ('2026-09-13',

2 row(s) inserted, 1 replaced

SELECT time, symbol, close FROM ochl_day ORDER BY symbol

(3 rows)

INSERT INTO ... VALUES

Syntax:

INSERT INTO table [(column, ...)] VALUES (value, ...) [, (value, ...) ...] [RETURNING ...]

Adds one row for each (...) after VALUES.

• Returns: N row(s) inserted.
• Each value is converted to the column type; see column types. Text goes in single quotes; a ' inside is written ''.
• Notes: many rows in one INSERT are much faster than one INSERT per row. A value cannot be a subquery or use a column. Every row needs the same number of values. The rules of the table (constraints) are checked for every row.

Example: Register two app users; the id and the time come by themselves

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, name STRING, created TIMESTAMP DEFAULT now());

INSERT INTO users (email, name) VALUES ('ram@example.com', 'Ram Sharma'), ('sita@example.com', 'Sita Rai');
SELECT id, email, name, created IS NOT NULL AS has_created FROM users;

Result:

INSERT INTO users (email, name) VALUES ('ram@example.com', 'Ram Sharma'), ('sita@example.com', 'Sita

2 row(s) inserted

SELECT id, email, name, created IS NOT NULL AS has_created FROM users

(2 rows)

Example: A forum post with text in another language (Nepali) and a quote inside

Setup:

CREATE TABLE posts (id LONG PRIMARY KEY AUTO, author STRING NOT NULL, body STRING NOT NULL);

INSERT INTO posts (author, body) VALUES
  ('ram', 'City''s dividend looks good.'),
  ('sita', 'नमस्ते, यो राम्रो छ।');
SELECT id, author, body FROM posts;

Result:

INSERT INTO posts (author, body) VALUES ('ram', 'City''s dividend looks good.'), ('sita', 'नमस्ते, य

2 row(s) inserted

SELECT id, author, body FROM posts

(2 rows)

Example: Values can be expressions without columns

Setup:

CREATE TABLE ledger (id LONG PRIMARY KEY AUTO, amount DECIMAL(2) NOT NULL, fee DECIMAL(2), created TIMESTAMP);

INSERT INTO ledger (amount, fee, created) VALUES (1000.00, round(1000.00 * 0.004, 2), now());
SELECT id, amount, fee, created > '2026-01-01' AS recent FROM ledger;

Result:

INSERT INTO ledger (amount, fee, created) VALUES (1000.00, round(1000.00 * 0.004, 2), now())

1 row(s) inserted

SELECT id, amount, fee, created > '2026-01-01' AS recent FROM ledger

(1 row)

Example: One bad row: no row of the statement is saved (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);

INSERT INTO brokers VALUES (1, 'Keystone Securities'), (2, NULL), (3, 'Vision Securities');

Result:

Error: row 2: column 'name' (STRING): cannot be NULL

Example: The wrong number of values (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);

INSERT INTO brokers VALUES (1, 'Keystone Securities'), (2);

Result:

Error: line 1, column 56: row 2 has 1 value; expected 2 like the first row

Duplicate keys

Syntax:

PRIMARY KEY (column)            relational table
DEDUP KEY (column, ..., time)   time-series table
UNIQUE (column)                 any table

How KDB decides that a new row is "already there":

• Duplicates inside one statement count too: with INSERT it is an error, with INSERT OR REPLACE the last one wins, with INSERT OR IGNORE the first one stays.
• Returns: a row with symbol='CITY', time='...' already exists in 'ochl_day'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it.
• Notes: a UNIQUE column is always an error on a clash, also with OR REPLACE and OR IGNORE: they only look at the primary key or dedup key. If you did not expect a duplicate, check the time zone (+05:45 missing?) and ROUND on the time column.

Example: A candle for the same symbol and day (shows an error)

Setup:

CREATE TABLE ochl_day (time TIMESTAMP ROUND 1d, symbol SYMBOL, close DOUBLE) TIMESERIES(time) DEDUP KEY (symbol, time);
INSERT INTO ochl_day VALUES ('2026-09-10', 'CITY', 504.9);

INSERT INTO ochl_day VALUES ('2026-09-10T14:00:00+05:45', 'CITY', 505.0);

Result:

Error: a row with symbol='CITY', time='2026-09-10T00:00:00.000Z' already exists in 'ochl_day'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it

Example: The same key twice in one INSERT OR REPLACE: the last row wins

Setup:

CREATE TABLE live_data (timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(timestamp) DEDUP KEY (timestamp, symbol);

INSERT OR REPLACE INTO live_data VALUES
  ('2026-09-10T11:05:00+05:45', 'CITY', 513.0),
  ('2026-09-10T11:05:00+05:45', 'CITY', 513.4);
SELECT timestamp, symbol, ltp FROM live_data;

Result:

INSERT OR REPLACE INTO live_data VALUES ('2026-09-10T11:05:00+05:45', 'CITY', 513.0), ('2026-09-10T1

1 row(s) inserted, 1 replaced

SELECT timestamp, symbol, ltp FROM live_data

(1 row)

INSERT OR REPLACE

Syntax:

INSERT OR REPLACE INTO table [(column, ...)] VALUES (...), ... [RETURNING ...]

Adds rows with new keys, and replaces rows whose key is already there. This is an upsert. It is the best statement for imports: the same file can be loaded again and again without double rows.

• The whole old row is replaced. Columns you do not give get their DEFAULT or NULL, not the old values. To change only some columns, use UPDATE.
• Returns: N row(s) inserted, M replaced.
• Notes: on a table without a key it works like INSERT. PostgreSQL writes INSERT ... ON CONFLICT (key) DO UPDATE; MySQL writes REPLACE INTO. QuestDB replaces rows by itself with DEDUP UPSERT KEYS; KDB does too on a table made with those words, and otherwise only when you write OR REPLACE (dedup_insert).

Example: The CITY close price was wrong: send the full row again

Setup:

CREATE TABLE ochl_day (time TIMESTAMP ROUND 1d, symbol SYMBOL, open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE, volume DOUBLE) TIMESERIES(time) DEDUP KEY (symbol, time);
INSERT INTO ochl_day VALUES ('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 508.2, 91000);

INSERT OR REPLACE INTO ochl_day VALUES ('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 507.6, 91000);
SELECT time, symbol, close, volume FROM ochl_day;

Result:

INSERT OR REPLACE INTO ochl_day VALUES ('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 507.6, 91000)

0 row(s) inserted, 1 replaced

SELECT time, symbol, close, volume FROM ochl_day

(1 row)

Example: Careful: columns you leave out lose their old values

Setup:

CREATE TABLE users (id LONG PRIMARY KEY, email STRING, phone STRING, verified BOOL DEFAULT FALSE);
INSERT INTO users VALUES (1, 'ram@example.com', '9800000000', TRUE);

INSERT OR REPLACE INTO users (id, email) VALUES (1, 'ram.sharma@example.com');
SELECT id, email, phone, verified FROM users;

Result:

INSERT OR REPLACE INTO users (id, email) VALUES (1, 'ram.sharma@example.com')

0 row(s) inserted, 1 replaced

SELECT id, email, phone, verified FROM users

(1 row)

INSERT OR IGNORE

Syntax:

INSERT OR IGNORE INTO table [(column, ...)] VALUES (...), ... [RETURNING ...]

Adds rows with new keys, and skips rows whose key is already there. The old row stays as it is.

• Returns: N row(s) inserted, M skipped (already there).
• Notes: use it when the first value must win: an imported trade list where rows never change, or "create the settings row if it is missing". RETURNING does not return skipped rows. PostgreSQL writes ON CONFLICT DO NOTHING.

Example: Load a trade log file again: old trades stay, new ones are added

Setup:

CREATE TABLE trade_log (date TIMESTAMP ROUND 1d, transaction_id LONG, symbol SYMBOL, quantity LONG, rate DOUBLE) TIMESERIES(date) PARTITION BY DAY DEDUP KEY (date, transaction_id);
INSERT INTO trade_log VALUES ('2026-09-10', 100009, 'COAST', 50, 400.0);

INSERT OR IGNORE INTO trade_log VALUES
  ('2026-09-10', 100009, 'COAST', 1, 1.0),
  ('2026-09-10', 100010, 'CITY', 50, 517.0);
SELECT transaction_id, symbol, quantity, rate FROM trade_log ORDER BY transaction_id;

Result:

INSERT OR IGNORE INTO trade_log VALUES ('2026-09-10', 100009, 'COAST', 1, 1.0), ('2026-09-10', 10001

1 row(s) inserted, 1 skipped (already there)

SELECT transaction_id, symbol, quantity, rate FROM trade_log ORDER BY transaction_id

(2 rows)

RETURNING

Syntax:

INSERT ... VALUES (...) RETURNING expression [[AS] alias], ... | *

Gives the inserted rows back, like a SELECT, with AUTO ids and DEFAULT values filled in. Your app gets the new id in the same statement.

• Returns: the rows, and the message N row(s) inserted.
• Notes: also works with UPDATE and DELETE (update and delete). With INSERT OR IGNORE, skipped rows are not returned. With INSERT OR REPLACE, every written row is returned, also one that a later row of the same statement replaced. If the RETURNING list has an error (an unknown column), nothing is written.

Example: Register a user and get the new id

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, created TIMESTAMP DEFAULT now());
INSERT INTO users (email) VALUES ('ram@example.com');

INSERT INTO users (email) VALUES ('sita@example.com') RETURNING id, email;

Result:

(1 row)

1 row(s) inserted

Example: Open accounts and see the defaults

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY AUTO, user_id LONG NOT NULL, balance DECIMAL(2) NOT NULL DEFAULT 0, status STRING DEFAULT 'active');

INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
INSERT INTO accounts (user_id) VALUES (2) RETURNING *;

Result:

INSERT INTO accounts (user_id, balance) VALUES (1, 1000)

1 row(s) inserted

INSERT INTO accounts (user_id) VALUES (2) RETURNING *

(1 row)

1 row(s) inserted

Example: A mistake in RETURNING: nothing is written (shows an error)

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE);

INSERT INTO users (email) VALUES ('ram@example.com') RETURNING user_id;
SELECT count(*) FROM users;

Result:

Error: RETURNING: line 1, column 8: unknown column 'user_id' (table 'users' has: id email)

Times and time zones in values

Syntax:

'YYYY-MM-DD[T| ]HH:MM[:SS[.fff]][Z | +HH:MM | +HHMM | -HH:MM]'

KDB keeps every TIMESTAMP in UTC. The text you write is read like this:

• A local time needs its zone offset, for example Nepal +05:45 or New York -04:00. In the sample data the market opens at 11:00 local time (+05:45) = 05:15 UTC.
• Notes: the most common mistake is a local time without its zone offset (for example +05:45): the row is then 5 hours 45 minutes wrong. The date must be YYYY-MM-DD with two digits for month and day. More in time and time zones and TIMESTAMP.

Example: Live prices written in Nepal time

Setup:

CREATE TABLE live_data (timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(timestamp) DEDUP KEY (timestamp, symbol);

INSERT INTO live_data VALUES
  ('2026-09-10T11:05:00+05:45', 'CITY', 513.0),
  ('2026-09-10T14:59:00+05:45', 'CITY', 527.1);
SELECT timestamp, symbol, ltp FROM live_data;

Result:

INSERT INTO live_data VALUES ('2026-09-10T11:05:00+05:45', 'CITY', 513.0), ('2026-09-10T14:59:00+05:

2 row(s) inserted

SELECT timestamp, symbol, ltp FROM live_data

(2 rows)

Example: Nepal time without +05:45 is stored 5:45 hours too late

Setup:

CREATE TABLE live_data (timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(timestamp);

INSERT INTO live_data VALUES ('2026-09-10 11:05:00', 'CITY', 513.0);
SELECT timestamp, symbol, ltp FROM live_data;

Result:

INSERT INTO live_data VALUES ('2026-09-10 11:05:00', 'CITY', 513.0)

1 row(s) inserted

SELECT timestamp, symbol, ltp FROM live_data

(1 row)

Example: A date in the wrong format (shows an error)

Setup:

CREATE TABLE live_data (timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(timestamp);

INSERT INTO live_data VALUES ('10/09/2026 11:05', 'CITY', 513.0);

Result:

Error: column 'timestamp' (TIMESTAMP): '10/09/2026 11:05' is not a time like '2026-09-11 11:00:00' (UTC) or '2026-09-11T11:00:00+05:45'

INSERT ... SELECT, DEFAULT VALUES

Syntax:

INSERT INTO table [(columns)] SELECT ...
INSERT INTO table DEFAULT VALUES

Copies the rows of a query into a table in one statement.

• Returns: the number of rows written. RETURNING works too.
• Notes:
  • The query must give one value for each target column, in order.
  • ON CONFLICT, OR REPLACE and OR IGNORE work with it (see ON CONFLICT).
  • DEFAULT VALUES writes one row with every column at its DEFAULT (or NULL).
  • Big copies: when the query is a plain read of tables (a WHERE and expressions are fine, UNION ALL too; no GROUP BY, DISTINCT, window or LIMIT), the rows go into the table while they are read. An ORDER BY of one table is fine too: the rows are sorted in batches of a few MB that go to temporary files in _tmp/ of the database folder (max_spill_bytes), then written in that order - the same rows as a sort in memory, so the ORDER BY still decides which of two rows with one key is kept. They are not held first as a query result, so --max-result-memory does not stop the copy; the rows count toward the transaction limits instead. It is still one statement: a bad row writes nothing, and the error names the row (row 17000: ...). This needs no RETURNING, no trigger on the table, and an ON CONFLICT only as DO NOTHING or a DO UPDATE that sets every other column from EXCLUDED. Any other query keeps its rows first, as before.
  • Into a table without a key (no DEDUP KEY, no PRIMARY KEY, not split by SYMBOL(...)), such a copy is written as columns, the way /imp writes: about 50 bytes a row in memory until the next checkpoint, not about 600. A loop copying 660k rows at a time stays near 80 MB of unsaved rows, not 750 MB.
  • Into empty partitions of a table with a key (a DEDUP KEY or PRIMARY KEY that is the whole SORT BY): when no partition the rows go to held a row before (a new month, or partitions a DELETE of the same transaction emptied) and the rows come in key order - as a copy from a table with the same SORT BY gives them - such a copy is written as columns too, and no key is looked up. A plain copy of every column, INSERT INTO t SELECT a, b, c FROM s, where s has the same columns and types, the same SORT BY and PARTITION BY, copies the partitions of s whole. The rows and the messages are the same as row by row: a key that repeats, or one already stored, sends the copy the row way, which reports it as before. KDB_NO_INSERT_KEY_RUNS=1 and KDB_NO_INSERT_COPY=1 turn these off.
  • A subquery can also be a value: VALUES ((SELECT max(id) FROM t) + 1, 'x').

Example: Copy the closes of one symbol into another table

Setup:

CREATE TABLE ochl_day (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time);
CREATE TABLE city_close (time TIMESTAMP, close DOUBLE) TIMESERIES(time);
INSERT INTO ochl_day VALUES ('2026-09-09T09:00:00Z', 'CITY', 510), ('2026-09-10T09:00:00Z', 'CITY', 513), ('2026-09-10T09:00:00Z', 'COAST', 900);

INSERT INTO city_close SELECT time, close FROM ochl_day WHERE symbol = 'CITY';
SELECT * FROM city_close ORDER BY time;

Result:

INSERT INTO city_close SELECT time, close FROM ochl_day WHERE symbol = 'CITY'

2 row(s) inserted

SELECT * FROM city_close ORDER BY time

(2 rows)

ON CONFLICT (PostgreSQL upsert)

Syntax:

INSERT INTO table (columns) VALUES (...) | SELECT ...
  ON CONFLICT [(key columns) [WHERE condition] | ON CONSTRAINT name]
  DO NOTHING
  | DO UPDATE SET column = EXCLUDED.column, ... [WHERE condition]
  [RETURNING ..., (xmax = 0) AS inserted]

What to do when a new row has the same key as a row that is there.

• DO NOTHING: skip that row.
• DO UPDATE: change the row that is there. EXCLUDED.column is the value of the new row; table.column is the value that is there now.
• Key columns: the primary key, a DEDUP KEY, a UNIQUE column or constraint, or a UNIQUE INDEX with exactly these columns. DO UPDATE needs them; DO NOTHING without them means any unique key.
• Partial unique index (CREATE UNIQUE INDEX ... WHERE ...): DO NOTHING without key columns skips a clash in it too. To name it, write its columns and a WHERE that holds every AND-ed part of the index's own WHERE (in any order; more parts are fine). A WHERE that does not is an error, as in PostgreSQL.
• RETURNING: the rows that were inserted or updated. xmax is 0 for an inserted row and 1 for an updated one.
• NULL: a key with a NULL never clashes.
• Notes:
  • Two new rows with the same key in one DO UPDATE statement are an error (as in PostgreSQL).
  • DO UPDATE SET of every other column from EXCLUDED is as fast as INSERT OR REPLACE.
  • Write the table name before a column (t.hits + 1); a plain name is ambiguous in PostgreSQL.

Example: Count visits per page: insert the first, update the rest

Setup:

CREATE TABLE page_hits (page STRING PRIMARY KEY, hits LONG NOT NULL);
INSERT INTO page_hits VALUES ('/chart', 1);

INSERT INTO page_hits (page, hits) VALUES ('/chart', 1), ('/news', 1)
ON CONFLICT (page) DO UPDATE SET hits = page_hits.hits + EXCLUDED.hits
RETURNING page, hits, (xmax = 0) AS inserted;

Result:

(2 rows)

1 row(s) inserted, 1 updated

Example: One open job per key: a partial unique index

Setup:

CREATE TABLE jobs (id INT, dedup_key STRING, status STRING);
CREATE UNIQUE INDEX jobs_open ON jobs (dedup_key) WHERE status <> 'done';
INSERT INTO jobs VALUES (1, 'report', 'queued');

INSERT INTO jobs VALUES (2, 'report', 'queued') ON CONFLICT DO NOTHING;
INSERT INTO jobs VALUES (3, 'report', 'queued') ON CONFLICT (dedup_key) WHERE status <> 'done' DO NOTHING;
SELECT id, status FROM jobs;

Result:

INSERT INTO jobs VALUES (2, 'report', 'queued') ON CONFLICT DO NOTHING

0 row(s) inserted, 1 skipped (already there)

INSERT INTO jobs VALUES (3, 'report', 'queued') ON CONFLICT (dedup_key) WHERE status != 'done' DO NO

0 row(s) inserted, 1 skipped (already there)

SELECT id, status FROM jobs

(1 row)