Create schemas and tables

Schemas and tables are created on the cluster's Data Explorer tab, which needs the Database Explorer role — see Who can use it. The cluster must be running.

Every change here goes the same way: the form writes the SQL and shows it under SQL preview, a dry run tries it in a transaction that is rolled back, and only then can you confirm it. Editing any field after the dry run means a new one.

What you create belongs to the database's owner — the user in the cluster's connection string — so your applications can use it at once, without grants.

Create a schema

1. In the tree on the Data Explorer tab, choose the Create schema button at the top.
2. Fill in Create schema:
   • Name — lowercase letters, digits and underscores, starting with a letter or an underscore, at most 63 characters;
   • Comment (optional) — up to 1024 characters;
   • IF NOT EXISTS — skip silently if a schema with that name exists.
3. Choose Dry run. When it passes, the sheet says Dry run passed — ready to commit.
4. Choose Confirm & create. The schema appears in the tree.

With SQL, on Query or with the CLI:

stsh pg query orders-db "create schema analytics"

Create a table

1. In the tree, choose Create table on the schema to create it in. The sheet's title, Create a new table under schema, lets you pick another schema.

2. Enter the table's Name, with the same rules as a schema name.

3. Define the Columns. The sheet starts with two: id, a bigint primary key that numbers itself, and created_at, a timestamptz that defaults to now(). For each column:

   • Name — the same rules as above, unique in the table;
   • Type — pick one of the common types, such as text, varchar(255), integer, bigint, numeric(10,2), boolean, uuid, timestamptz or jsonb, or choose Custom… and write another type, optionally with a size such as (64) or (10,2);
   • Default — an SQL expression, such as now() or 'pending'; the field suggests one for the type;
   • Primary — part of the primary key. Mark several columns for a key made of all of them;
   • Null — whether the column may be empty. A primary key column may not;
   • Auto — the column numbers itself. Only for smallint, integer and bigint, and not together with a Default.

   Add column adds one; drag a row to reorder.

4. Optionally turn on IF NOT EXISTS and add a Comment (optional).

5. Choose Review & save for the dry run, then Confirm & create.

Foreign keys, indexes and unique constraints are not in the form; add them with SQL on Query once the table exists. A table whose primary key is a single column can be edited on Browse; see Browse and edit rows.

Drop a schema or a table

Dropping needs postgrescluster/schemaDrop, which the Database Explorer role does not include — see Who can use it.

1. In the tree, choose the drop button on the schema or the table.
2. Turn on CASCADE to drop what depends on it as well — foreign keys, views and so on. Without it, the drop is refused if anything depends on it.
3. Choose Dry run. The result lists the objects the drop would take with it.
4. Type the object's name to confirm and choose the Drop button. This cannot be undone, other than by restoring a snapshot.

If it is refused

• Denied — missing permissions. — the result names the permissions you lack, and which of them just-in-time access could grant.
• Refused — structurally not allowed. — the statement is one the Data Explorer never runs; see Refused statements.
• An error with an SQL state, such as [42P07], comes from PostgreSQL itself — for example, an object with that name already exists.