CREATE SCHEMA

Give your account a schema of its own, a named place for the tables your product needs.

Syntax

Write CREATE SCHEMA and the name you want.

CREATE SCHEMA name

Parameters and arguments

  • name

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

    Required: Yes

Write the short name only. HTQLDB adds your account's prefix when it creates the schema, and the answer tells you the full name.

Examples

Make a place for the tables of one purpose.

Return type and behaviour

CREATE SCHEMA answers one record: the short name you wrote, the full name HTQLDB gave the schema, and the schema's identifier.

{
  "name": "warehouse",
  "schema": "<your account's prefix>_warehouse",
  "schemaId": "<the schema's identifier>"
}

Running the statement again for a schema your account already has answers the same way and changes nothing.

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 SCHEMA 'name' must be lower snake case and start with a letterThe name has a capital, a dash, a space or starts with a digitUse lower case letters, digits and underscores
HTQL-2001 CREATE SCHEMA 'name' uses a reserved prefixThe name starts with pg_ or tenant_Choose another name
HTQL-2001 '…' is longer than 63 characters once prefixedThe name, with your account's prefix, is too long for the databaseShorten the name
HTQL-2001 that schema belongs to another accountThe name is registered to a different accountChoose another name
Placement and context

CREATE SCHEMA is a statement of its own. Run it before the CREATE TABLE statements that name the schema with IN.

Limitations
  • A schema holds tables. To give a table its own place, name the schema in CREATE TABLE with IN.
  • A name that is already registered to another account is refused.
  • Names are lower case. A capital letter, a space, a dash or a dot is refused.
Permissions and security

CREATE SCHEMA 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
  • Group the tables of one purpose in one schema, such as warehouse for stock tables.
  • Choose short, plain names. The full name is longer, because it carries your account's prefix.