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 the databases that have been created. 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, which is similar to common relational databases. 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 schemas that have been named. 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, you must specify the table in the <Schema name.table name> 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 the search_path parameter is not specified, the schema named public is used as the default schema. You can execute a statement to modify the search_path value. The following example demonstrates 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. Tablespaces can be used to store different database objects on 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, different databases can be stored 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;