AnalyticDB for PostgreSQL:Logical structure of database objects

Databases

An AnalyticDB for PostgreSQL instance may consist of multiple databases. You can execute the \l statement in the psql tool to view existing databases. Sample query result:

postgres=# \l
                                      List of databases
   Name    |   Owner    | Encoding |  Collate   |   Ctype    |       Access privileges
-----------+------------+----------+------------+------------+-------------------------------
 diskquota | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 | =c/"xiaoxia.zj"              +
           |            |          |            |            | "xiaoxia.zj"=CTc/"xiaoxia.zj"
 template1 | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 | =c/"xiaoxia.zj"              +
           |            |          |            |            | "xiaoxia.zj"=CTc/"xiaoxia.zj"
 zj        | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 |

Tables, indexes, and views

AnalyticDB for PostgreSQL organizes data in tables in the same manner that common relational databases are organized. AnalyticDB for PostgreSQL also provides objects such as indexes, views, and sequences. For more information, see the official PostgreSQL documentation.

Schemas

A database consists of one or more named schemas. A schema contains tables and other objects such as data types, functions, and operators.

Execute the following statement to create a schema:

CREATE SCHEMA myschema;

When you create or query a table, specify the table in the schemaName.tableName format. Example:

CREATE TABLE myschema.mytable(···)···;
SELECT * FROM myschema.mytable;

You can set the search_path parameter to specify a list of schemas that can be searched for objects. The default schema is the first schema in the list. If you do not specify the search_path parameter, the schema named public is used as the default schema. You can execute a statement to change the value of search_path. The following example shows how to modify the default schema:

ALTER DATABASE mydatabase SET search_path TO myschema;

Tablespaces

A tablespace allows you to define an alternative location on the file system where the data files that contain database objects reside. You can use tablespaces to store different database objects in different locations. For example, you can store frequently used tables on SSDs and other tables on traditional disks.

Logically, different tablespaces are used to store different database objects. For example, you can store different databases in different tablespaces to differentiate their physical locations.

You can execute the \l+ statement to view tablespaces.

postgres=# \l+
 diskquota | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 |                               | 69 MB   | pg_default |
 postgres  | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 |                               | 72 MB   | pg_default | default administrative connection database
 template0 | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 | =c/"xiaoxia.zj"              +| 69 MB   | pg_default | unmodifiable empty database
           |            |          |            |            | "xiaoxia.zj"=CTc/"xiaoxia.zj" |         |            |
 template1 | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 | =c/"xiaoxia.zj"              +| 69 MB   | pg_default | default template for new databases
           |            |          |            |            | "xiaoxia.zj"=CTc/"xiaoxia.zj" |         |            |
 zj        | xiaoxia.zj | UTF8     | en_US.utf8 | en_US.utf8 |                               | 1062 MB | pg_default |

If you have the CREATE permission on a tablespace, you can create database objects such as tables, indexes, and databases in the tablespace. Example:

CREATE TABLE tablename(options) TABLESPACE spacename;