SQL REFERENCE

Data Manipulation Language

Insert, update, and delete data in tables.

INSERT INTO ... VALUES

Syntax:

INSERT INTO table_name VALUES (val1, val2, ...);

Single row:

INSERT INTO t VALUES (1, 1.5, 'alice');

Multi-row:

INSERT INTO t VALUES (1, 'x'), (2, 'y'), (3, 'z');

Negative numbers are supported in VALUES:

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (-5), (10), (-3);

Column-List INSERT

You can specify an explicit column list to control insertion order or insert into a subset of columns:

INSERT INTO t (name, id) VALUES ('alice', 1);
INSERT INTO t (id, val) VALUES (1, 3.14), (2, 2.72);

Columns not listed receive default values (0 for integers, NaN for floats, empty string for VARCHAR).

Temporal Literals in INSERT

DATE, TIME, and TIMESTAMP columns accept string literals in standard SQL format, as well as raw integer values:

CREATE TABLE events (id INTEGER, name VARCHAR, event_date DATE, event_time TIME);
INSERT INTO events VALUES (1, 'Launch', '2025-03-15', '09:00:00');
INSERT INTO events VALUES (2, 'Meeting', '2025-03-16', '14:30:00');

-- Timestamps also accept string literals
CREATE TABLE logs (id INTEGER, created_at TIMESTAMP);
INSERT INTO logs VALUES (1, '2025-06-15 09:30:00');

Accepted formats:

INSERT INTO ... SELECT

INSERT INTO archive SELECT * FROM orders WHERE date < '2024-01-01';

Example:

CREATE TABLE t1 (id INTEGER, val REAL, name VARCHAR);
INSERT INTO t1 VALUES (1, 1.5, 'alice'), (2, 2.5, 'bob');

CREATE TABLE t2 (id INTEGER, val REAL, name VARCHAR);
INSERT INTO t2 SELECT * FROM t1 WHERE id <= 2;

UPDATE

Syntax:

UPDATE table_name SET col1 = expr1 [, col2 = expr2, ...] [WHERE condition];

Examples:

-- Update all rows
UPDATE t SET val = 100.0;

-- Update with a condition
UPDATE t SET val = 100.0 WHERE id = 1;
UPDATE t SET name = 'updated' WHERE val > 50.0;

-- Update multiple columns
UPDATE t SET val = val * 2, name = 'doubled' WHERE id > 5;

The WHERE clause supports the same expressions as SELECT (comparisons, AND, OR, NOT, IS NULL, etc.).

DELETE

Syntax:

DELETE FROM table_name [WHERE condition];

Examples:

-- Delete specific rows
DELETE FROM t WHERE id = 3;
DELETE FROM t WHERE val < 0 AND name = 'old';

-- Delete all rows (truncate)
DELETE FROM t;

DELETE FROM t; without a WHERE clause truncates the table, removing all rows but preserving the table schema and any embedding column metadata.

Embedding Column Restrictions

Tables with high-dimensional embedding columns (dimension > 1) have DML restrictions because the C engine's filter and update kernels operate element-wise on flat F32 arrays and are not dimension-aware: