INSERT
Add a record with a single statement.
Syntax
Give the record as a JSON object, then the table.
INSERT {"column": value, …} INTO table
INSERT EDGE {"_from": gid, "_to": gid, "key": value, …} IN name_edgeParameters and arguments
{…}The record: column names and their values. Every name must be a column of the table. It cannot be empty
Required: YesvalueA value is a literal, a list, a nested object, a variable, a function's result such as
Required: YesTODAY()orNOW(), a default such asx ?? "none", or, inside a FOR loop, a column of the current row such aso.totaltableThe table to add to, written after
Required: YesINTO. It must be a table your account may write__idOptional, for a table that issues identifiers. A number or text that the record's generated identifier is built from, in place of a fresh one
Required: No
For an edge, INSERT EDGE takes _from and _to (the identifiers of the two ends) and any other keys, which are kept as the edge's metadata. The collection's name is lower case with the suffix _edge, such as placed_by_edge.
Examples
One stock record in a table of your own called inventory.
Return type and behaviour
INSERT answers that it succeeded and returns no rows. It does not return the new record, so read it back with LOOKUP if you need it.
The caller's tenant is added to the record when the table has a tenant column and you did not name one. You never have to write tenant_id.
When the table issues identifiers, the record's identifier is generated unless you supply the column yourself.
INSERT EDGE keys an edge by its pair of ends. Inserting the same _from and _to again updates that edge's metadata rather than adding a second edge. An edge cannot join an end to itself.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-1002 | The record is empty, or the table is missing | Write INSERT {"column": value} INTO table |
HTQL-3010 Table is read-only | The table is a platform table or is not one your account may write | Insert into a table your account may write |
HTQL-4015 | A name in the record is not a column of the table | Check the column's name |
HTQL-6011 | The edge collection's name is not lower case with the _edge suffix | Name it like placed_by_edge |
HTQL-6012 | An edge is missing _from or _to, sets an engine-owned key (edgeId, _from_type, _to_type, tenant_id), joins an end to itself, or names an end that does not exist | Fix the edge as the message says |
Placement and context
INSERT is a statement of its own. It can also appear in the body of a FOR loop to add a record for each row. A LET before it can hold a value the record uses. The keywords are not case sensitive.
Limitations
INSERTwrites one record each time. To add many, loop with FOR.- The integer
numIdcolumn is issued by the table and cannot be written. - An edge's ends must be identifiers in the form
gid://namespace/resource/id. Where the resource is one of the platform's own record types, the record must exist. - Write the database's own column names. A name that is not a column is refused.
Permissions and security
INSERT runs as the caller, confined by row-level security to the caller's own account. A platform table such as orders refuses a direct write with HTQL-3010: its records change through the statement that owns them, such as INSERT ORDER {…}. A content table is written by a caller who holds the content area, and a link collection by the account that owns it.
A column classified above the caller's clearance cannot be written by that caller. The statement is refused, never partly applied.
Best practices
- Leave
tenant_idout. HTQLDB adds yours. - Use UPSERT when the record may already exist.
- Read the record back with LOOKUP when you need its generated identifier.
- Keep a value you use twice in a LET, so the record cannot disagree with itself.