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_edge

Parameters and arguments

  • {…}

    The record: column names and their values. Every name must be a column of the table. It cannot be empty

    Required: Yes
  • value

    A value is a literal, a list, a nested object, a variable, a function's result such as TODAY() or NOW(), a default such as x ?? "none", or, inside a FOR loop, a column of the current row such as o.total

    Required: Yes
  • table

    The table to add to, written after INTO. It must be a table your account may write

    Required: Yes
  • __id

    Optional, 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
ErrorWhat happenedHow to recover
HTQL-1002The record is empty, or the table is missingWrite INSERT {"column": value} INTO table
HTQL-3010 Table is read-onlyThe table is a platform table or is not one your account may writeInsert into a table your account may write
HTQL-4015A name in the record is not a column of the tableCheck the column's name
HTQL-6011The edge collection's name is not lower case with the _edge suffixName it like placed_by_edge
HTQL-6012An 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 existFix 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
  • INSERT writes one record each time. To add many, loop with FOR.
  • The integer numId column 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_id out. 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.