CREATE SCHEMA¶
In PostgreSQL, a database can contain multiple schemas, and public schema is the default configuration. Each user can use a database with his/her own schema, without interfering with each other, and it is also easy for administrators to maintain the database.
When a new schema is added to the Hologres, the storage structure of a table will be changed from database.table to database.schema.table. Each table belongs to a schema, and a database can have multiple schemas. Different schema can have the same object (table name, data type, etc.).
currently, Hologres supports create/alter/rename schema, and create table for a schema. Drop schema is not supported by now.
Public Schema¶
Every database has a default schema, which is a public schema. And the tables created will be stored in the public schema by default.
to view the current schema is as following:
select current_schema();
\d tablename; // describe a table, and it also shows the schema it belongs to. it can only be called in the terminal
Create Schema¶
Create a new schema in a database is as following:
create schema schemaname;
set search_path to schemaname;
creat table blink_demo (id text);
select current_schema();
\d blink_demo; //describe table
Create tables across Schema¶
Hologres supports the operation of tables of not only current schema but also other schemas by adding “schema_name.” in front of the table name. Example is as following:
1.Create a table for the public schema
create table public.mytest (
name text,
id int);
2.create table for schema my_schema in public schema, the example is as following
set search_path to public;
create table my_schema.mytest (
name text,
id int,
age int
);