CREATE SCHEMA - Hologres - Alibaba Cloud ドキュメントセンター

Hologres allows you to create a table across schemas. This topic describes how to create a schema in a Hologres database and how to create a table across schemas.

Background information

Hologres is compatible with PostgreSQL and supports the same schema-related operations as PostgreSQL.

After the schema feature is enabled for Hologres, the storage structure of a table changes from database.table to database.schema.table.

The current version of Hologres allows you to create schemas, rename schemas, and create tables in schemas.

In Hologres, each table belongs to one schema, and a database can contain multiple schemas. Schemas function as logical groups of tables to facilitate management. Schemas also allow multiple users to use the same database without interfering with each other.

The same table name or data type can be used in different schemas without conflict.

Hologres automatically creates a schema named public for each new database. If you do not create a schema, all tables created in a database are stored in the public schema. To check the current schema, execute one of the following statements:

SELECT CURRENT_SCHEMA(); -- Check the current schema. 
\d tablename; -- Check the schema to which the desired table belongs. This statement applies only to terminals.

The following figure shows the hierarchical relationship of Hologres instance objects. 层级关系

Procedure

Create a schema.

To create a schema in a database and create a table in the schema, execute the following sample SQL statements:

CREATE SCHEMA schemaname; -- Create a schema. 
SET search_path TO schemaname; -- Switch to the new schema. 
CREATE TABLE blink_demo (id text); -- Create a table in the new schema. 
SELECT CURRENT_SCHEMA(); -- Check the current schema.

Create a table across schemas.
To create a table across schemas, specify the table name in the format of Schema name.Table name in the table creation statement. Sample statements:
- Create a table in the public schema based on the current schema.
```
CREATE TABLE public.mytest (
  name text,
  id INT);
```
- Switch to the public schema and create a table in the my_schema schema.
```
SET search_path TO public;
CREATE TABLE my_schema.mytest (
  name text,
  id INT,
  age INT
);
```

Other supported operations

You can execute the following SQL statement to query the total storage size of all the tables in a schema:

SELECT table_schema, pg_size_pretty(SUM(pg_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))::decimal)) AS schema_size
FROM information_schema.tables 
WHERE table_schema = '<schema_name>'-- Replace <schema_name> with the name of a specified schema.
GROUP BY table_schema;