CREATE SCHEMA

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

Background information

Compatible with PostgreSQL, Hologres supports the same schema-related operations as PostgreSQL.

After the schema feature is enabled for Hologres, the qualified name format of a table changes from Database name.Table name to Database name.Schema name.Table name.

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 database objects to ease management. In addition, the use of schemas allows 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 any schemas, 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 target table belongs. This statement applies only to terminals.

The hierarchical relationship of hologres instance objects is shown in the following figure.

1. Create a schema.
   To create a schema in a database and create a table in the schema, execute the following 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.

2. Create a table across schemas.
   To create a table across schemas, specify the table name in the format of Schema name.Table name. The following examples demonstrate how to create a table across schemas:

   • To create a table named mytest in the public schema from the current schema, execute the following SQL statement:

     create table public.mytest (
       name text,
       id int);

   • Suppose the current schema is my_schema. To switch to the public schema and create a table in the my_schema schema, execute the following SQL statements:

     set search_path to public;
     create table my_schema.mytest (
       name text,
       id int,
       age int
     );