CREATE TABLE

Create a table of your own and say what each field holds.

Syntax

Write CREATE TABLE and the table's name, add IN and a schema to place it, then WITH FIELDS and an object that describes each field.

CREATE TABLE name WITH FIELDS {"field": {"data_type": "Type", option, …}, …}
CREATE TABLE name IN schema WITH FIELDS {…}

Parameters and arguments

  • name

    The table's short name. Lower case letters, digits and underscores, starting with a letter. It may not start with pg_ or tenant_ or end with _edge, and once your account's prefix is added it must fit in 63 characters

    Required: Yes
  • IN schema

    A schema your account made with CREATE SCHEMA, by its short name. Without it the table is created in your account's own schema

    Required: No
  • WITH FIELDS {…}

    An object with one entry per field. The entry's key is the field's name, lower case letters, digits and underscores, starting with a letter. At least one field is needed, and a field may not be named tenant_id

    Required: Yes

Field types. Each field's data_type is written exactly as shown, with a capital first letter.

data_typeHoldsYou write and read it as
StringTextText in quotes
IntegerA whole number12
FloatA number with a fraction, kept as an approximation3.75
MoneyAn amount of money, kept exactly in the currency's smallest unitThe amount in the main unit, 12.50
PercentageA rate such as a tax rate, kept exactlyThe percent, 7.5 for seven and a half per cent
RatioA proportion such as a yield, kept exactlyThe decimal, 0.82
DateA calendar dayText, "2026-11-20"
TimeA time of dayText, "14:30:00"
DateTime, TimestampA moment, with its time zoneText, "2026-11-20T14:30:00Z"
BooleanTrue or falsetrue
Array, ObjectA JSON list, or a JSON record, stored whole[1, 2], {"a": 1}

Null is refused: a field needs a type. Any other word is refused too.

Field options. Write them beside data_type.

OptionEffect
primary_keyThe field is part of the row's identity. Mark one field, or several for a composite key. A key field is always required
requiredThe row cannot be written without a value for this field
is_uniqueNo two rows of your account may share a value. A primary_key field is already unique, so it needs no is_unique
default_valThe value an INSERT gets when it leaves the field out. It must be the field's own kind: a whole number for Integer, text for String, true or false for Boolean, a list for Array, a record for Object. Money, Percentage and Ratio take no default
max_lengthThe most characters a String may hold, from 1 to 10485760. It applies to String only
min_lengthThe fewest characters a String may hold. HTQLDB checks it on write
auto_incrementThe database counts upward for each new row. Integer only, and it takes no default_val
descriptionA note about the field, kept with the table

Keys. If no field is marked primary_key, HTQLDB adds a text field named id as the key, filled with a random unique value whenever an INSERT leaves it out. If you declare a field called id yourself, mark it primary_key. See GIDs for when you need an identifier of your own.

Names you read with. The name you write for a field is the name you use in INSERT, UPDATE, GET and in every answer. Money, Percentage and Ratio fields are stored in whole numbers, and HTQLDB converts on write and on read by the field's name, so "unit_price": 12.50 goes in and unit_price 12.50 comes out. A Money value uses the row's currency field when the table has one, and your account's currency when it has not. To keep several currencies in one table, add a currency String field.

Examples

A table with a text key, a name and a count.

Return type and behaviour

CREATE TABLE answers one record: the short name, the full name HTQLDB gave the table, the schema it was placed in and the number of columns it holds. The count includes the account identifier column HTQLDB adds to every table, and the id key when it added one.

{
  "name": "inventory",
  "table": "<your account's prefix>_inventory",
  "schema": "<the schema's full name>",
  "columns": 3
}

Afterwards you reach the table by its short name in INSERT, GET, LOOKUP and the other statements, and LIST TABLES shows it with both names.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-3002 Unauthorized accessThe caller is not the account's ownerAsk the account's owner to run it
HTQL-2001 CREATE TABLE 'name' must be lower snake case and start with a letterThe table name has a capital, a dash, a space or starts with a digitUse lower case letters, digits and underscores
HTQL-2001 CREATE TABLE 'name' uses a reserved prefixThe name starts with pg_ or tenant_Choose another name
HTQL-2001 a name ending _edge is an edge collectionThe table name ends in _edgeChoose another name
HTQL-2001 schema 'name' is not one this account createdIN names a schema your account does not ownRun CREATE SCHEMA first, and check the name
HTQL-2001 field 'name' must be lower snake case and start with a letterA field name is not lower caseRename the field
HTQL-2001 field 'name' is reserved or too longThe field is called tenant_id or is longer than 63 charactersRename the field
HTQL-2001 a field named 'id' must be marked primary_key, or renamedA field called id is not the keyMark it primary_key or rename it
HTQL-2001 field 'name': …The field's own definition is wrong: an unknown data_type or option, Null, a default_val of the wrong kind or on a Money, Percentage or Ratio field, max_length on a field that is not String, auto_increment on a field that is not Integer, or a relationshipRead the reason and correct the field
HTQL-2001 CREATE TABLE WITH FIELDS needs an object of fieldsWITH FIELDS is empty or is not an objectDescribe at least one field
HTQL-6001 create table 'name': …The database refused the table, for example because it already exists or the schema is not registered to your accountRead the reason in the message
Placement and context

CREATE TABLE is a statement of its own. Run it before the INSERT statements that fill the table, and after the CREATE SCHEMA that its IN names.

Limitations
  • The table belongs to your account. Every row carries your account's identifier and the database returns only your account's rows. Platform tables, such as orders and customers, are not created or changed this way.
  • A table needs a primary key, which HTQLDB supplies when you do not.
  • A field cannot carry a relationship to another table.
  • A table that already exists in the schema, under the same name, is refused.
  • A name ending _edge is reserved for edge collections.
  • Rules about a value, such as min_length and max_length, are checked by HTQLDB when you write. The database itself holds no such rule.
Permissions and security

CREATE TABLE is for the account's owner. The caller must hold the Merchant owner role for the account the request runs in. A manager, a member or any other role is refused, and so is an owner of a different account.

Best practices
  • Use Money for amounts, Percentage for rates and Ratio for proportions, instead of Float, so totals stay exact.
  • Declare the key yourself when rows already have a natural identity, such as a SKU or an order number.
  • Add a currency String field when one table holds amounts in more than one currency.
  • Mark required the fields a row is meaningless without.