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 nameParameters and arguments
nameThe schema's short name. Lower case letters, digits and underscores, starting with a letter. It may not start with
Required: Yespg_ortenant_, and once your account's prefix is added it must fit in 63 characters
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
| 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 SCHEMA 'name' must be lower snake case and start with a letter | The name has a capital, a dash, a space or starts with a digit | Use lower case letters, digits and underscores |
HTQL-2001 CREATE SCHEMA 'name' uses a reserved prefix | The name starts with pg_ or tenant_ | Choose another name |
HTQL-2001 '…' is longer than 63 characters once prefixed | The name, with your account's prefix, is too long for the database | Shorten the name |
HTQL-2001 that schema belongs to another account | The name is registered to a different account | Choose 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
warehousefor stock tables. - Choose short, plain names. The full name is longer, because it carries your account's prefix.