Skip to Content
SQL ReferenceData Manipulation

Data Manipulation (DML)

Standard SQL data manipulation commands for reading and writing data.

DML permission and routing behavior depends on table type. For USER, SHARED, STREAM, and SYSTEM access rules, see /docs/server/architecture/table-types.

INSERT

Insert one or more rows into a table:

SQL
INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)VALUES (<value1>, <value2>, ...);

Batch Insert

SQL
INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)VALUES  (<value1a>, <value2a>, ...),  (<value1b>, <value2b>, ...);

Examples

SQL
-- Single insert with auto-generated IDINSERT INTO chat.messages (conversation_id, sender, content)VALUES (1, 'alice', 'Hello!'); -- Batch insertINSERT INTO chat.messages (conversation_id, sender, role, content)VALUES  (1, 'alice', 'user', 'What is KalamDB?'),  (1, 'assistant', 'assistant', 'KalamDB is a SQL-first realtime database.');

Upsert with ON CONFLICT

KalamDB supports PostgreSQL-style upsert for literal INSERT ... VALUES statements on USER, SHARED, and STREAM tables:

SQL
INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)VALUES (<value1>, <value2>, ...)ON CONFLICT (<primary_key_column>)DO UPDATE SET  <column1> = EXCLUDED.<column1>,  <column2> = <literal_value>;

If the primary key already exists, KalamDB updates the row with the DO UPDATE SET assignments. If the primary key is new, KalamDB inserts the row from VALUES.

Examples

SQL
-- Update an existing shared row by primary keyINSERT INTO app.items (id, name)VALUES (1, 'beta')ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; -- Insert when the primary key is missing, update when it already existsINSERT INTO app.items (id, name)VALUES (42, 'gamma')ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

DO UPDATE SET assignments support:

  • EXCLUDED.<column> to read the value from the attempted insert
  • literal constants such as 'published', 42, or NULL

Upsert inside explicit transactions

Upsert works inside BEGIN / COMMIT blocks and sees rows staged earlier in the same transaction, including on SHARED tables:

SQL
BEGIN; INSERT INTO app.items (id, name) VALUES (2, 'alpha'); INSERT INTO app.items (id, name) VALUES (2, 'gamma')ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; COMMIT;

Outside an explicit transaction, KalamDB wraps each upsert in its own internal transaction and commits it automatically.

Upsert rules and limits

  • Requires a single-column primary key. The conflict target must be that primary key column.
  • Literal VALUES inserts only. INSERT ... SELECT upserts are not supported on this path.
  • ON CONFLICT DO NOTHING, ON CONFLICT ON CONSTRAINT, and ON CONFLICT DO UPDATE WHERE are not supported.
  • Tuple assignments in DO UPDATE SET are not supported.
  • system.* tables are rejected.

RETURNING on upsert

Add RETURNING to an upsert to get back the inserted or updated row instead of only an affected-row count. This follows PostgreSQL’s INSERT ... RETURNING shape: the clause lists the output columns, and aliases are supported.

SQL
INSERT INTO [<namespace>.]<table_name> (<columns>)VALUES (<values>)ON CONFLICT (<primary_key_column>)DO UPDATE SET <assignments>RETURNING <column> [, <column> AS <alias> ...];

Examples

SQL
-- Return the final row after an updateINSERT INTO app.items (id, name)VALUES (1, 'beta')ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.nameRETURNING id, name; -- Return a column aliasINSERT INTO app.items (id, name)VALUES (1, 'beta')ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.nameRETURNING id, name AS returned_name; -- Return the inserted row when no conflict occurredINSERT INTO app.items (id, name)VALUES (42, 'alpha')ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.nameRETURNING id, name;

Response shape

  • Without RETURNING, the SQL API reports an insert result with rows_affected.
  • With RETURNING, the SQL API returns a query result with schema, rows, and row_count, the same success shape as SELECT.

RETURNING is currently supported on the upsert path above. Plain INSERT ... RETURNING without ON CONFLICT, and UPDATE / DELETE ... RETURNING, are not supported yet.

UPDATE

SQL
UPDATE [<namespace>.]<table_name>SET <column1> = <value1>, <column2> = <value2>WHERE <condition>;

Example

SQL
UPDATE chat.messagesSET content = 'Updated message content'WHERE id = 42;

DELETE

SQL
DELETE FROM [<namespace>.]<table_name>WHERE <condition>;

Example

SQL
DELETE FROM chat.messagesWHERE conversation_id = 1 AND sender = 'bot';

Explicit Transactions

KalamDB stays in autocommit mode by default. Use explicit transaction blocks when you need PostgreSQL-style atomic multi-statement writes.

Syntax

SQL
BEGIN;START TRANSACTION; COMMIT;COMMIT WORK; ROLLBACK;ROLLBACK WORK;

Commit example

SQL
BEGIN; INSERT INTO chat.messages (conversation_id, sender, content)VALUES (7, 'alice', 'draft'); UPDATE chat.messagesSET content = 'published'WHERE conversation_id = 7 AND sender = 'alice'; COMMIT;

Rollback example

SQL
BEGIN; INSERT INTO chat.messages (conversation_id, sender, content)VALUES (8, 'alice', 'temporary'); SELECT *FROM chat.messagesWHERE conversation_id = 8; ROLLBACK;

Within the open transaction, reads see the staged writes from that same transaction. After ROLLBACK, those staged changes are discarded.

Rules and limits

  • Explicit transactions currently support USER and SHARED tables.
  • STREAM tables and system.* tables are rejected inside explicit transactions.
  • DDL statements are not supported inside explicit transactions.
  • Nested BEGIN and savepoints are not supported in this phase.
  • A single /v1/api/sql request can contain multiple sequential BEGIN ... COMMIT or BEGIN ... ROLLBACK blocks.
  • If a /v1/api/sql request ends with an open transaction, KalamDB rolls it back automatically.

To inspect live transaction state, query system.transactions. For pg-extension-specific session state, use system.sessions. See /docs/server/sql-reference/system-views.

For deeper read-query patterns (joins, CTEs, and DataFusion-compatible advanced SELECT usage), see /docs/server/sql-reference/query-data.

Last updated on
Table DDLQuery Data (SELECT & JOIN)