1. Overview

An OushuDB cluster manages multiple databases, each database contains multiple schemas, and one schema contains multiple objects (tables, views, functions, etc.), so the hierarchical structure between these objects is:

database -> schema -> (tables, functions, views) Each schema, table, view, function, etc. belongs to only one database. This chapter mainly introduces the common usage of each object. Please refer to the reference manual for specific syntax.

2. Database

After the initialization of OushuDB, three databases will be generated by default. You can use the l command to view or view pg_database system table.

postgres=# \l
                  List of databases
   Name    |  Owner   | Encoding | Access privileges
-----------+----------+----------+-------------------
 postgres  | ChangLei | UTF8     |
 template0 | ChangLei | UTF8     |
 template1 | ChangLei | UTF8     |
(4 rows)

template0 and template1 are template databases. Template1 is the template database used by the system to create a new database by default. You can modify it. template0 does not accept connections by default, so it cannot be changed. The purpose is to always save a clean template database.

When creating a database, you can specify the template database of a database. The default is template1. Now OushuDB only supports template0, template1 and postgres databases as template databases. For example:

postgres=＃ create database tdb; # Create a new database with template0 as the template by default
CREATE DATABASE

postgres=＃\c postgres  # Connect postgres
postgres=＃ create table test(i int);  # Create the test table in the postgres database
CREATE TABLE

postgres=＃ create table test_orc(i int) with (appendonly=true, orientation=orc);  # Create ORC format table in postgres database
CREATE TABLE

postgres=# create database dbnew template postgres;
CREATE DATABASE

postgres=＃\c dbnew # Connect dbnew

As you can see, dbnew now contains the test table

dbnew=＃\d
               List of relations
 Schema | Name | Type  |  Owner   |   Storage
--------+------+-------+----------+-------------
 public | test | table | ChangLei | append only
(1 row)

3. Pattern

A database contains multiple schemas, and a schema can contain multiple named objects, such as tables, data types, functions, operators, etc. The same object name can be used in different schemas without conflict. For example, schema1 can contain the table test, and schema2 can also contain the table named test. In this sense, a schema is much like a namespace.

When an object is created, it is placed in public mode by default. The following is the default schema created by the system.

template1=# \dn
       List of schemas
        Name        |  Owner
--------------------+----------
 hawq_toolkit       | ChangLei
 information_schema | ChangLei
 pg_aoseg           | ChangLei
 pg_bitmapindex     | ChangLei
 pg_catalog         | ChangLei
 pg_toast           | ChangLei
 public             | ChangLei
(7 rows)

Generally, in these scenarios, users need to use patterns:

• Allow multiple users to use a database at the same time without name conflict.
• Organize database objects into multiple schema s as if they were multiple namespaces
• Third party applications can put their objects into a separate schema without generating slave graphs from other objects.

Note: schemas cannot be nested, that is, schemas can no longer be included in schemas.

The following is an example of creating a schema.

create schema myschema;

To create or access an object in a schema, you can use the form of {schema}.{object}, for example

create table myschema.test(i int);
select * from myschema.test;

To delete an empty schema, you can use:

drop schema myschame;

To delete a non empty schema, you can use the cascade keyword:

drop schema myschema cascade;

It is usually not very convenient to use the form of {schema}.{object}. It can be simplified by setting the schema search path. " SHOW search_ The "path" command can give the current schema search path. " SET search_ "Path to schema-name1, schema-name2" can set the schema search path. For example:

postgres=＃ show search_path;
  search_path
----------------
 "$user",public
(1 row)

postgres=＃ create schema myschema;
CREATE SCHEMA

postgres=＃ set search_path = public, myschema;
SET

postgres=＃ show search_path;
   search_path
------------------
 public, myschema
(1 row)