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
nameThe table's short name. Lower case letters, digits and underscores, starting with a letter. It may not start with
Required: Yespg_ortenant_or end with_edge, and once your account's prefix is added it must fit in 63 charactersIN schemaA schema your account made with CREATE SCHEMA, by its short name. Without it the table is created in your account's own schema
Required: NoWITH 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
Required: Yestenant_id
Field types. Each field's data_type is written exactly as shown, with a capital first letter.
data_type | Holds | You write and read it as |
|---|---|---|
String | Text | Text in quotes |
Integer | A whole number | 12 |
Float | A number with a fraction, kept as an approximation | 3.75 |
Money | An amount of money, kept exactly in the currency's smallest unit | The amount in the main unit, 12.50 |
Percentage | A rate such as a tax rate, kept exactly | The percent, 7.5 for seven and a half per cent |
Ratio | A proportion such as a yield, kept exactly | The decimal, 0.82 |
Date | A calendar day | Text, "2026-11-20" |
Time | A time of day | Text, "14:30:00" |
DateTime, Timestamp | A moment, with its time zone | Text, "2026-11-20T14:30:00Z" |
Boolean | True or false | true |
Array, Object | A 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.
| Option | Effect |
|---|---|
primary_key | The field is part of the row's identity. Mark one field, or several for a composite key. A key field is always required |
required | The row cannot be written without a value for this field |
is_unique | No two rows of your account may share a value. A primary_key field is already unique, so it needs no is_unique |
default_val | The 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_length | The most characters a String may hold, from 1 to 10485760. It applies to String only |
min_length | The fewest characters a String may hold. HTQLDB checks it on write |
auto_increment | The database counts upward for each new row. Integer only, and it takes no default_val |
description | A 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
| Error | What happened | How to recover |
|---|---|---|
HTQL-3002 Unauthorized access | The caller is not the account's owner | Ask the account's owner to run it |
HTQL-2001 CREATE TABLE 'name' must be lower snake case and start with a letter | The table name has a capital, a dash, a space or starts with a digit | Use lower case letters, digits and underscores |
HTQL-2001 CREATE TABLE 'name' uses a reserved prefix | The name starts with pg_ or tenant_ | Choose another name |
HTQL-2001 a name ending _edge is an edge collection | The table name ends in _edge | Choose another name |
HTQL-2001 schema 'name' is not one this account created | IN names a schema your account does not own | Run CREATE SCHEMA first, and check the name |
HTQL-2001 field 'name' must be lower snake case and start with a letter | A field name is not lower case | Rename the field |
HTQL-2001 field 'name' is reserved or too long | The field is called tenant_id or is longer than 63 characters | Rename the field |
HTQL-2001 a field named 'id' must be marked primary_key, or renamed | A field called id is not the key | Mark 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 relationship | Read the reason and correct the field |
HTQL-2001 CREATE TABLE WITH FIELDS needs an object of fields | WITH FIELDS is empty or is not an object | Describe 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 account | Read 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
_edgeis reserved for edge collections. - Rules about a value, such as
min_lengthandmax_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
currencyString field when one table holds amounts in more than one currency. - Mark
requiredthe fields a row is meaningless without.