sql - 更改 PostgreSQL 数据库中的模式结构

Question

我有一个带有一些模式(都具有相同结构)的 Postgres 数据库，我想知道是否可以更改同一数据库中所有模式的结构(表名、新列等)。数据库中模式的可能或用途是什么？

谢谢。

Answer 1

我将重点关注你问题的后半部分，因为我认为它会回答前半部分(而且我不确定我理解上半场)。

what's the purpose of the schemas in a database?

---

当我第一次从 MySQL 切换到 PostgreSQL 时，这让我感到困惑。 Postgres 模式本质上与 MySQL 数据库相同。事实上，根据 MySQL Reference Manual :

In MySQL, physically, a schema is synonymous with a database.

---

这就引出了什么是 PostgreSQL 数据库的问题？来自PostgreSQL Documentation :

More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

---

所以 PostgreSQL 数据库本质上是模式的集合？看起来有点毫无意义，为什么我们需要层次结构中的那一步？让我们看一下 PostgreSQL schema 的文档:

A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

---

因此，在 PostgreSQL 中，模式包含表、函数等。数据库管理用户/组连接以及对特定模式集群的访问/角色。通常，我在一个数据库下工作，并将信息分解为模式以分段信息。