Postgresql官方文档之DML

原创 _ All China Database Union 2024-04-07

728

一、Generated Columns

生成列是一种特殊的列，它的值总是根据其他列计算得出。这类似于视图对于表的作用。生成列分为两种类型：存储的（stored）和虚拟的（virtual）。存储的生成列在写入（插入或更新）时计算，并占用存储空间，就像普通列一样。虚拟的生成列不占用存储空间，而是在读取时计算，因此虚拟生成列类似于视图，而存储的生成列类似于物化视图（但总是自动更新的）。目前，PostgreSQL仅实现了存储的生成列。
要创建生成列，可以在CREATE TABLE命令中使用GENERATED ALWAYS AS子句。例如：


CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED

);

在这个例子中，height_in列是一个生成列，它的值总是根据height_cm列计算得出。关键字STORED必须被指定来选择生成列的存储类型。
生成列不能直接写入。在INSERT或UPDATE命令中，不能为生成列指定值，但可以指定关键字DEFAULT。
还需要注意生成列和有默认值的列之间的区别。具有默认值的列在首次插入行时评估一次（如果没有提供其他值）；生成列在行更改时更新，且不能被覆盖。默认值不能引用表中的其他列；生成表达式通常会这样做。默认值可以使用易变函数，例如random()或引用当前时间的函数；这对于生成列是不允许的。
对于生成列和包含生成列的表的定义，有几个限制：

生成表达式只能使用不可变函数，不能使用子查询或以任何方式引用当前行之外的内容。
生成表达式不能引用另一个生成列。
生成表达式不能引用系统列，除了tableoid。
生成列不能有列默认值或身份定义。
生成列不能是分区键的一部分。
外部表可以有生成列。
此外，生成列在访问权限上与其基础列分开维护，因此可以安排某些角色可以从生成列读取但不能从基础列读取。

二、Constraints

1、Check Constraints

检查约束是一种非常通用的约束类型，它允许你指定一个布尔（真值）表达式，数据表中的每条记录都必须满足这个表达式才被认为是有效的。例如，如果你有一个存储产品信息的表，你可能希望确保产品价格总是正数。这可以通过在创建表时添加一个检查约束来实现：


CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

在这个例子中，CHECK (price > 0)就是一个检查约束，它确保了price列中的每个值都大于0。检查约束定义在列的数据类型之后，就像默认值定义一样。默认值和约束可以按任意顺序列出。
你还可以给约束命名，这会使错误信息更清晰，并允许你在需要修改约束时引用它。语法如下：


CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

在这里，CONSTRAINT positive_price CHECK (price > 0)创建了一个名为positive_price的检查约束，功能与之前的例子相同。
检查约束还可以引用多个列。假设你存储了常规价格和折扣价格，并希望确保折扣价格低于常规价格：


CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

前两个约束保证了价格和折扣价格都是正数。第三个约束，CHECK (price > discounted_price)，确保了折扣价格低于常规价格。这个约束不附加在特定列上，而是作为列定义列表中的单独项出现。列定义和这种约束定义可以混合排序。
需要注意的是，如果检查约束的表达式计算结果为真或者null值，那么约束被认为是满足的。因为大多数表达式如果任何操作数为null，都会计算为null值，所以它们不会阻止列中的null值。要确保列不包含null值，可以使用非空约束。

2、Not-Null Constraints

非空约束指定一个列不能接受null值。这是通过在列定义时添加NOT NULL来实现的。例如，如果你有一个产品信息表，你可能希望确保每个产品都有一个产品编号和名称，这可以通过在创建表时为这些列添加非空约束来实现：


CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric

);

在这个例子中，product_no和name列都有NOT NULL约束，这意味着尝试插入一个没有product_no或name的记录将会导致错误。price列没有这样的约束，所以它可以接受null值。
非空约束总是作为列约束来写。它们是一种确保数据完整性的基本方式，因为它们可以防止在关键列中插入不完整的记录。在数据库设计中，标记为非空的列通常是表的关键部分，它们对于每条记录来说都是必需的。
需要注意的是，非空约束不能防止向列中插入空字符串或零（对于数值类型的列），这些值在数据库中被视为有效值。非空约束仅仅确保列中的值不是null。
非空约束是数据库设计中用于强制执行字段必填规则的一种常见方法。通过使用非空约束，可以保证数据库中的数据满足基本的完整性和有效性要求。

3、Unique Constraints

唯一约束确保在一个表中的一个列或一组列的所有值都是唯一的。这意味着表中的任何两行都不能在指定列上有相同的值组合。唯一约束不仅可以用于单个列，也可以跨多个列定义。
例如，如果你有一个产品表，你可能希望确保每个产品的产品编号（product_no）都是唯一的，这可以通过在创建表时为product_no列添加唯一约束来实现：


CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

在这个例子中，product_no列有一个UNIQUE约束，这意味着尝试插入一个已经存在的product_no值的记录将会导致错误。
唯一约束也可以跨多个列定义，以确保列的组合是唯一的。例如，如果你有一个存储员工信息的表，你可能希望确保每个员工的名字和姓氏的组合是唯一的：


CREATE TABLE employees (
    first_name text,
    last_name text,
    UNIQUE (first_name, last_name)
);

在这个例子中，first_name和last_name列的组合有一个唯一约束，这意味着尝试插入两个员工具有相同名字和姓氏的记录将会导致错误。
你还可以为唯一约束命名，这在需要引用或修改约束时是有用的：


CREATE TABLE products (
    product_no integer CONSTRAINT unique_product_no UNIQUE,
    name text,
    price numeric

);

在这里，CONSTRAINT unique_product_no UNIQUE创建了一个名为unique_product_no的唯一约束。
添加唯一约束将自动创建一个唯一的B树索引来强制执行约束条件。这也意味着唯一约束的列可以利用索引来提高查询性能。然而，如果数据中存在大量的null值，需要注意的是，在唯一约束的上下文中，多个null值被认为是不相等的，因此一个唯一约束的列允许有多个null值。
此外，唯一约束可以通过指定NULLS NOT DISTINCT选项来调整对null值的处理方式，使得列中的null值被视为相等，从而在列中只允许一个null值。这种行为与SQL标准的默认行为不同，因此在设计跨数据库平台的应用程序时需要特别注意。

4、Primary Keys

主键约束是确保表中每行都有一个唯一标识的方式。这意味着主键列中的值必须是唯一的，并且不能为null。主键可以是表中的单个列，或者是多个列的组合，这种情况下，列的组合必须是唯一的。
例如，如果你有一个产品信息表，你可能希望每个产品都有一个唯一的产品编号（product_no），可以通过在创建表时为product_no列添加主键约束来实现：


CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric

);

在这个例子中，product_no列被指定为主键，这意味着它既是唯一的，也不允许为null。
主键也可以跨多个列定义，以确保列的组合是唯一的。例如，如果你有一个存储员工信息的表，你可能希望确保每个员工的名字和姓氏的组合是唯一的：


CREATE TABLE employees (
    first_name text,
    last_name text,
    PRIMARY KEY (first_name, last_name)
);

在这个例子中，first_name和last_name列的组合被指定为主键，这意味着尝试插入两个员工具有相同名字和姓氏的记录将会导致错误。
添加主键约束将自动创建一个唯一的B树索引来强制执行约束条件，并且会强制列为NOT NULL。这也意味着主键的列可以利用索引来提高查询性能。
一个表最多只能有一个主键。虽然表可以有多个唯一约束和非空约束，这些在功能上几乎与主键相同，但只有一个约束可以被标识为主键。关系数据库理论指出，每个表都必须有一个主键，这是为了确保数据的完整性和唯一性。在实践中，主键对于文档目的和客户端应用程序非常有用，例如，一个GUI应用程序允许修改行值可能需要知道表的主键以能够唯一地识别行。此外，数据库系统使用主键作为外键引用其表的默认目标列。

5、Foreign Keys

外键约束指定了一个表中的列（或一组列）必须匹配另一个表中的行的值。这种机制用于维护两个相关表之间的引用完整性，确保数据之间的一致性和准确性。
外键约束可以确保一个表（称为引用表或子表）中的数据项在另一个表（称为被引用表或父表）中有对应的条目。这样，外键约束有助于实现表之间的关系，特别是在实现一对多关系时。
例如，假设有一个products表，其中每个产品都有一个唯一的product_no，还有一个orders表，用于存储产品订单信息。我们希望确保orders表中的每个订单都引用了一个存在于products表中的有效产品。这可以通过在orders表上创建一个外键约束来实现：


CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric

);
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer

);

在这个例子中，orders表的product_no列是一个外键，它引用了products表的product_no列。这意味着尝试插入一个orders记录，其product_no值在products表中不存在将会导致错误。
外键约束还支持级联操作，比如当被引用的行被删除或更新时，可以自动更新或删除引用该行的外键行。这是通过在外键约束定义中指定ON DELETE和ON UPDATE规则来实现的。
外键约束不仅保证了数据的引用完整性，还通过定义表之间的关系，支持了数据库的逻辑结构和数据的一致性。外键在数据库设计中是实现实体之间关系的重要工具，特别是在实现关系数据库的规范化设计时。

6、Exclusion Constraints

排他约束是一种强大的数据完整性工具，允许你指定一个表中的行必须在某种意义上是“不相交”的。这意味着你可以定义一组规则，这些规则通过指定的操作符来比较一组列或表达式的值，以确保没有两行违反这些规则。
排他约束的一个常见用例是防止时间段重叠。例如，如果你有一个会议室预订系统，你可能想要确保同一个会议室的预订时间段不会重叠。使用排他约束，你可以强制执行这一规则，确保在任何给定时间，一个会议室只能被预订一次。
排他约束的定义使用EXCLUDE关键字，后面跟上一组使用特定操作符比较的列或表达式。例如，假设有一个reservations表，其中包含会议室预订的开始时间和结束时间，你可以这样定义一个排他约束，以防止时间段重叠：


CREATE TABLE reservations (
    room text,
    start_time timestamp,
    end_time timestamp,
    EXCLUDE USING gist (room WITH =, tsrange(start_time, end_time) WITH &&)
);

在这个例子中，EXCLUDE USING gist指定了排他约束使用GiST索引来维护。room WITH =表示在比较时将使用等于操作符来比较room列的值，而tsrange(start_time, end_time) WITH &&表示将使用&&操作符（表示范围重叠）来比较由start_time和end_time定义的时间范围。因此，这个排他约束确保了对于任意两行，如果它们的room值相同，则它们的时间范围不会重叠。
排他约束提供了一种灵活而强大的方式来维护复杂的数据完整性规则，它超越了传统的唯一约束和外键约束所能提供的功能。通过使用排他约束，开发者可以在数据库层面上强制执行复杂的业务逻辑规则，从而减少应用程序逻辑的复杂性并提高数据的一致性。

三、系统列

这些系统列被隐式地定义，因此在数据库中占据特殊位置，用于内部跟踪和管理目的。用户在定义表结构时无需显式声明这些系统列，但应该了解它们的存在和作用。以下是一些主要的系统列：

tableoid: 表的OID（对象标识符）。这个列特别有用，当从分区表或继承的表层次结构中查询时，它可以帮助识别一行数据来自哪个具体的表。tableoid可以与pg_class系统表中的oid列连接，以获取表的名称。
xmin: 插入当前行版本的事务的ID。在PostgreSQL中，每个事务都有一个唯一的事务ID，xmin列记录了创建当前行版本的事务ID。
cmin: 在插入事务中，当前行版本被插入的命令计数器（从零开始）。
xmax: 删除当前行版本的事务的ID，或者对于未删除的行版本，此列为零。有时这个列的非零值表示删除该行的事务尚未提交，或者尝试删除该行的操作被回滚了。
cmax: 在删除事务中，当前行版本被删除的命令计数器，或者对于未删除的行版本，此列为零。
ctid: 行版本在其表中的物理位置。虽然ctid可以被用来非常快速地定位行版本，但是如果行被更新或者通过VACUUM FULL操作移动，ctid会发生变化。因此，它不适合作为长期的行标识符。在需要长期标识行的场景中，应该使用主键。
这些系统列为PostgreSQL的MVCC（多版本并发控制）模型提供了支持，使得数据库能够管理并发访问、事务和行版本。

四、修改表

1、Adding a Column

这是通过使用ALTER TABLE命令实现的，该命令允许你在不删除现有数据的情况下修改表的结构。向表中添加列是数据库维护和演化中常见的需求，尤其是当业务需求发生变化时。
例如，如果你有一个名为products的表，现在需要在其中添加一个名为description的列来存储产品的描述信息，你可以使用如下命令：


ALTER TABLE products ADD COLUMN description text;

这个命令会在products表中添加一个新的列，名为description，数据类型为text。新添加的列将被添加到表的现有列之后。如果没有为新列指定默认值，那么现有的行在这个新列中的值将为null。
此外，你还可以在添加列时指定一个默认值。例如，如果你想要为新添加的description列指定一个空字符串作为默认值，可以使用如下命令：


ALTER TABLE products ADD COLUMN description text DEFAULT '';

这意味着，对于表中现有的每一行，新列description将被填充为空字符串。对于之后插入的任何新行，如果在插入时没有为description列提供值，它也将被设置为空字符串。
从PostgreSQL 11开始，添加带有常数默认值的列不再需要重写整个表，这大大减少了添加列的成本。然而，如果默认值是易变的（例如，来自一个函数调用），那么每一行将需要更新以填充新列的值。
总的来说，ALTER TABLE ... ADD COLUMN命令提供了一种灵活的方式来扩展现有表的结构，以适应应用程序的发展需求，同时保持数据的完整性和连续性。

2、Removing a Column

这是通过使用ALTER TABLE命令和DROP COLUMN子句实现的。删除列是数据库维护过程中的一个操作，可能因为某些数据不再需要或者表结构需要调整。
例如，如果你有一个名为products的表，现在决定不再需要存储产品的description列，你可以使用如下命令来删除这个列：


ALTER TABLE products DROP COLUMN description;

这个命令会从products表中移除description列，以及该列中的所有数据。需要注意的是，执行此操作后，与该列相关的所有数据将被永久删除，这个操作是不可逆的。
如果尝试删除的列不存在，PostgreSQL会报错。然而，在SQL脚本文件中，常见的做法是无条件地尝试删除每个表之前可能存在的列，并忽略任何错误消息，以确保脚本无论列是否存在都能正常工作。为了避免错误消息，你可以使用DROP COLUMN IF EXISTS变体，这样如果列不存在，PostgreSQL将忽略删除操作并继续执行，不会报错：


ALTER TABLE products DROP COLUMN IF EXISTS description;

如果你需要修改已存在的列，而不是完全删除它，可以查看5.6节后面的内容，那里讨论了如何使用ALTER TABLE命令来修改现有列的定义。
删除列是调整数据库结构的一种方式，但在执行此操作之前应该仔细考虑，因为它会导致数据的永久丢失。在删除重要数据之前，建议进行备份，以防需要恢复数据。

3、Adding a Constraint

这是通过使用ALTER TABLE命令和ADD CONSTRAINT子句实现的。约束是用于强制表中数据遵守特定规则的规则，例如唯一性、主键、外键、检查（验证特定条件）等。
例如，如果你有一个employees表，并且想要确保email列中的值是唯一的，你可以添加一个唯一约束：


ALTER TABLE employees ADD CONSTRAINT email_unique UNIQUE (email);

在这个例子中，ADD CONSTRAINT email_unique UNIQUE (email)命令向employees表添加了一个名为email_unique的唯一约束，这意味着email列中的所有值必须是唯一的，任何尝试插入重复email值的操作都会被拒绝。
同样，你也可以添加其他类型的约束。例如，如果你想要确保salary列中的值永远不会低于0，你可以添加一个检查约束：


ALTER TABLE employees ADD CONSTRAINT salary_check CHECK (salary >= 0);

在这个例子中，ADD CONSTRAINT salary_check CHECK (salary >= 0)命令向employees表添加了一个名为salary_check的检查约束，确保salary列中的值不会低于0。
添加约束是数据库设计和维护的重要方面，它有助于保证数据的完整性和正确性。在添加约束时，PostgreSQL会检查现有数据是否满足新的约束条件。如果现有数据违反了要添加的约束，命令将失败并显示错误消息。因此，在添加约束之前，确保现有数据符合新约束的要求是很重要的。
还要注意，向大表添加约束可能是一个耗时的操作，因为PostgreSQL需要扫描整个表以确保现有数据符合约束条件。在生产环境中进行此类操作时，应考虑可能的性能影响。

4、Removing a Constraint

这是通过使用ALTER TABLE命令和DROP CONSTRAINT子句实现的。删除表约束是数据库维护的一部分，可能是因为业务规则的变化或者约束不再需要。
例如，如果你之前在employees表上添加了一个名为email_unique的唯一约束，现在因为业务需求的变化，需要允许email列中有重复的值，你可以删除这个唯一约束：


ALTER TABLE employees DROP CONSTRAINT email_unique;

在这个例子中，DROP CONSTRAINT email_unique命令从employees表中移除了名为email_unique的唯一约束。执行此操作后，email列将不再强制其值是唯一的，允许插入重复的电子邮件地址。
删除约束时，需要知道约束的名称。如果约束是在创建表时或使用ALTER TABLE ADD CONSTRAINT时明确指定的，那么使用该名称。如果约束名称未明确指定，PostgreSQL会自动生成一个名称，这种情况下，你需要通过查询数据库元数据（例如，使用\d命令在psql中查看表结构）来找出约束的名称。
需要注意的是，删除约束不会影响表中已经存在的数据，只会影响之后的数据插入或更新操作。因此，在删除约束之前，应该仔细考虑这一操作对数据完整性的影响。
此外，如果你尝试删除一个不存在的约束，PostgreSQL会报错。为了避免这种错误，你可以使用DROP CONSTRAINT IF EXISTS变体，这样如果约束不存在，操作将被安全地忽略，而不会产生错误：


ALTER TABLE employees DROP CONSTRAINT IF EXISTS email_unique;

删除约束是调整数据库结构以适应变化需求的一种方式，但在执行此操作之前应该仔细考虑，因为它可能会放宽对数据完整性的保护。在删除重要约束之前，进行数据备份是一个好习惯，以防需要恢复。

5、Changing a Column’s Default Value

这是通过使用ALTER TABLE命令和ALTER COLUMN子句配合SET DEFAULT或DROP DEFAULT操作来实现的。更改列的默认值允许你调整表的行为以适应应用程序的变化需求，而不影响表中已经存在的数据。
例如，如果你有一个products表，其中的price列当前没有默认值，现在你想设置一个默认值，以便在未指定价格时自动将价格设置为9.99，你可以使用如下命令：


ALTER TABLE products ALTER COLUMN price SET DEFAULT 9.99;

这个命令会将products表的price列的默认值设置为9.99。之后，当插入新行而没有为price列指定值时，price列将自动使用9.99作为其值。
如果某个列已经有了一个默认值，但你想更改它，可以用同样的方法来设置一个新的默认值。PostgreSQL会替换旧的默认值。
另一方面，如果你想移除某个列的默认值，使得该列没有默认值，可以使用DROP DEFAULT操作。例如，如果price列当前有一个默认值，但你现在想要删除它，可以使用如下命令：


ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这个命令会移除products表的price列的默认值。之后，如果在插入新行时没有为price列指定值，该列将会是null（假设该列允许null值）。
需要注意的是，更改列的默认值只影响之后插入或更新表时的行为，对表中已经存在的数据没有影响。默认值在插入新记录时应用，或者在通过INSERT命令使用DEFAULT关键字显式请求默认值时应用。
更改列的默认值是数据库维护和适应变化需求的一种常见操作，它提供了灵活性，使得数据库设计可以随着时间和应用程序需求的变化而变化。

6、Changing a Column’s Data Type

这是通过使用ALTER TABLE命令和ALTER COLUMN子句配合TYPE关键字来实现的。更改列的数据类型允许你调整表的结构以适应数据的新需求或更正设计错误，但这个操作需要谨慎进行，因为它可能会影响数据的完整性和应用程序的兼容性。
例如，如果你有一个products表，其中的price列当前被定义为整型（integer），但你现在想将其更改为数值型（numeric）以支持小数，你可以使用如下命令：


ALTER TABLE products ALTER COLUMN price TYPE numeric;

这个命令会将products表的price列的数据类型从integer更改为numeric。PostgreSQL会尝试将现有数据自动转换为新的数据类型。如果现有数据可以无损地转换为新类型，这个操作将成功。
如果转换需要特定的处理逻辑，或者PostgreSQL无法自动转换数据类型，你可以使用USING子句来指定如何将旧数据转换为新的数据类型。例如，如果你想将文本类型的列转换为整型，并且该列包含一些非数字字符，你可能需要提供一个表达式来提取或转换数据：


ALTER TABLE products ALTER COLUMN product_code TYPE integer USING (trim(product_code)::integer);

在这个例子中，USING子句通过trim(product_code)::integer表达式来转换数据，首先去除文本值两端的空格，然后将结果转换为整型。
更改列的数据类型是一个强大的功能，但在执行此操作时需要特别注意：

确保新的数据类型与列中的现有数据兼容。
考虑到更改数据类型可能会影响依赖于该列的数据库对象，例如视图、索引和外键约束。
在生产环境中，建议先在测试环境中测试数据类型更改的影响，以确保应用程序的正常运行。
考虑备份相关数据，以防数据类型更改导致数据丢失或损坏。
更改列的数据类型提供了调整和优化数据库结构的灵活性，但必须谨慎使用以避免不必要的数据问题。

7、Renaming a Column

这是通过使用ALTER TABLE命令和RENAME COLUMN子句实现的。重命名列是数据库维护的一部分，可能是因为需要更清晰地反映列中数据的含义、业务需求的变化，或者是为了遵循新的命名约定。
例如，如果你有一个名为products的表，并且想要将product_no列重命名为product_number，你可以使用如下命令：


ALTER TABLE products RENAME COLUMN product_no TO product_number;

这个命令会将products表中的product_no列的名称更改为product_number。执行此操作后，所有引用该列的查询和操作都需要使用新的列名。
重命名列是一个相对简单的操作，但它会直接影响到依赖于该列的数据库对象（如视图和触发器）以及应用程序代码。因此，在进行列重命名之前，应该仔细考虑这一操作对现有系统的影响，并确保更新所有相关的引用和文档。
请注意，虽然重命名列本身是一个快速操作，但更新所有依赖项可能需要更多时间和精力。在生产环境中执行此类更改时，建议在计划的维护窗口内进行，并在更改前进行充分的测试，以确保系统的平稳过渡。
总的来说，ALTER TABLE ... RENAME COLUMN命令提供了一种灵活的方式来调整数据库列的名称，使其更好地符合业务逻辑、数据模型或命名标准。

8、Renaming a Table

这是通过使用ALTER TABLE命令和RENAME TO子句实现的。重命名表是数据库维护的一部分，可能是因为需要更清晰地反映表中数据的含义、业务需求的变化，或者是为了遵循新的命名约定。
例如，如果你有一个名为products的表，并且想要将其重命名为items，你可以使用如下命令：


ALTER TABLE products RENAME TO items;

这个命令会将products表的名称更改为items。执行此操作后，所有引用该表的查询和操作都需要使用新的表名。
重命名表是一个相对简单的操作，但它会直接影响到依赖于该表的数据库对象（如视图、索引、外键和触发器）以及应用程序代码。因此，在进行表重命名之前，应该仔细考虑这一操作对现有系统的影响，并确保更新所有相关的引用和文档。
请注意，虽然重命名表本身是一个快速操作，但更新所有依赖项可能需要更多时间和精力。在生产环境中执行此类更改时，建议在计划的维护窗口内进行，并在更改前进行充分的测试，以确保系统的平稳过渡。
总的来说，ALTER TABLE ... RENAME TO命令提供了一种灵活的方式来调整数据库表的名称，使其更好地符合业务逻辑、数据模型或命名标准。

五、Privileges

在PostgreSQL中，权限（或访问权）控制哪些用户或角色可以对数据库中的对象执行哪些操作。这是数据库安全管理的一个关键方面，确保数据不会被未经授权的用户访问或修改。
权限系统支持多种不同类型的权限，包括但不限于：

SELECT: 允许用户读取表、视图或物化视图中的数据。
INSERT: 允许用户向表或视图中添加新行。
UPDATE: 允许用户修改表或视图中现有行的数据。
DELETE: 允许用户从表或视图中删除行。
TRUNCATE: 允许用户快速删除表中的所有行。
REFERENCES: 允许用户在一个表中创建引用另一个表的外键约束。
TRIGGER: 允许用户在表上创建或触发触发器。
CREATE: 允许用户创建新的数据库对象，如表、视图或索引。
CONNECT: 允许用户连接到数据库。
TEMPORARY 或 TEMP: 允许用户创建临时表。
EXECUTE: 允许用户执行存储的函数或过程。
USAGE: 允许用户使用特定的数据库对象，如序列或架构。
权限可以通过GRANT命令授予用户或角色，也可以通过REVOKE命令收回。例如，授予用户对表的SELECT权限：


GRANT SELECT ON table_name TO user_name;

或收回该权限：


REVOKE SELECT ON table_name FROM user_name;

PostgreSQL还支持通过PUBLIC关键字将权限授予所有用户，以及使用角色（用户组）来管理权限，这样可以更方便地同时控制多个用户的权限。
对象的拥有者（通常是创建对象的用户）和超级用户默认具有对对象的所有权限，并且可以授予或收回其他用户对这些对象的权限。此外，拥有者可以将对象的所有权转让给另一个用户。
权限系统是保护数据库安全、确保数据完整性和遵守访问控制策略的重要工具。

访问控制列表（ACL）的权限类型及其缩写。这些缩写用于表示特定对象（如表、函数等）上授予或撤销的权限。以下是表中提到的权限及其对应的缩写和适用对象类型：

SELECT ®: 允许读取表、大对象、序列等的数据。对于序列，这还包括使用currval函数。
INSERT (a): 允许在表中插入新数据。
UPDATE (w): 允许更新表中的现有数据。对于序列，这还包括使用nextval和setval函数。
DELETE (d): 允许从表中删除数据。
TRUNCATE (D): 允许使用TRUNCATE命令快速清空表。
REFERENCES (x): 允许在一个表中创建引用另一个表的外键约束。
TRIGGER (t): 允许用户在表上创建或触发触发器。
CREATE ©: 对于数据库，允许在数据库中创建新的架构和数据库级别的对象。对于架构，允许在架构中创建新的对象。对于表空间，允许创建使用该表空间的数据库和对象。
CONNECT ©: 允许用户连接到数据库。
TEMPORARY (T): 允许用户创建临时表。
EXECUTE (X): 允许用户执行存储的函数或过程。
USAGE (U): 对于架构，允许用户访问架构中的对象。对于序列和类型（包括域），允许使用它们。对于外部数据封装器和外部服务器，允许在其上创建新的服务器或用户映射。
SET (s): 允许用户在会话中设置配置参数的值。
ALTER SYSTEM (A): 允许用户使用ALTER SYSTEM命令更改服务器级别的配置。

PostgreSQL中不同类型的SQL对象（如数据库、表、函数等）上可用的访问权限以及这些权限的默认设置。

数据库 (DATABASE):
- 所有权限: CTc
- 默认PUBLIC权限: Tc
- 查看权限的psql命令: \l
域 (DOMAIN):
- 所有权限: U
- 默认PUBLIC权限: U
- 查看权限的psql命令: \dD+
函数或过程 (FUNCTION or PROCEDURE):
- 所有权限: X
- 默认PUBLIC权限: X
- 查看权限的psql命令: \df+
外部数据封装器 (FOREIGN DATA WRAPPER):
- 所有权限: U
- 默认PUBLIC权限: none
- 查看权限的psql命令: \dew+
外部服务器 (FOREIGN SERVER):
- 所有权限: U
- 默认PUBLIC权限: none
- 查看权限的psql命令: \des+
语言 (LANGUAGE):
- 所有权限: U
- 默认PUBLIC权限: U
- 查看权限的psql命令: \dL+
大对象 (LARGE OBJECT):
- 所有权限: rw
- 默认PUBLIC权限: none
- 查看权限的psql命令: \dl+
配置参数 (PARAMETER):
- 所有权限: sA
- 默认PUBLIC权限: none
- 查看权限的psql命令: \dconfig+
架构 (SCHEMA):
- 所有权限: UC
- 默认PUBLIC权限: none
- 查看权限的psql命令: \dn+
序列 (SEQUENCE):
- 所有权限: rwU
- 默认PUBLIC权限: none
- 查看权限的psql命令: \dp
表 (TABLE) 和类似表的对象:
- 所有权限: arwdDxt
- 默认PUBLIC权限: none
- 查看权限的psql命令: \dp
表空间 (TABLESPACE):
- 所有权限: C
- 默认PUBLIC权限: none
- 查看权限的psql命令: \db+
类型 (TYPE):
- 所有权限: U
- 默认PUBLIC权限: U
- 查看权限的psql命令: \dT+
  这个表提供了一个快速参考，帮助理解不同类型的数据库对象支持哪些权限，以及如何通过psql命令查询这些权限。默认PUBLIC权限列显示了在对象创建时自动授予所有用户的权限，这有助于理解哪些操作是默认允许的。

六、Row Security Policies

行级安全策略是PostgreSQL提供的一种数据访问控制机制，它允许数据库管理员定义安全策略来控制基于用户（或角色）的访问权限，从而精细控制不同用户可以查看或修改表中哪些行。
主要内容包括：

启用行级安全：通过ALTER TABLE ... ENABLE ROW LEVEL SECURITY命令启用特定表的行级安全。默认情况下，表上不启用行级安全，意味着没有额外的策略限制对表中数据的访问。
定义安全策略：使用CREATE POLICY命令在启用了行级安全的表上定义安全策略。策略可以基于SQL表达式来限制对行的访问，这些表达式通常涉及到当前用户的信息（例如，使用current_user或session_user）。
策略的类型：
- 使用策略（USING）：定义哪些行对查询（SELECT）操作可见。
- 检查策略（WITH CHECK）：定义哪些行可以被插入或修改。这通常用于INSERT和UPDATE操作。
策略的应用场景：行级安全策略不仅可以应用于限制数据的查询，还可以限制数据的修改。例如，可以定义策略来确保用户只能修改他们自己创建的记录。
特殊角色的行为：表的拥有者和超级用户默认绕过行级安全策略。如果需要，可以通过ALTER TABLE ... FORCE ROW LEVEL SECURITY命令强制表的拥有者也受到行级安全策略的约束。
性能考虑：行级安全策略的使用可能会对查询性能产生影响，因为每次访问受保护的表时都需要评估策略定义的条件。
行级安全策略提供了一种强大的机制来实现基于数据行的细粒度访问控制，适用于多租户应用、用户数据隔离等场景。正确和谨慎地使用行级安全策略可以显著提高数据的安全性和隐私保护水平。

七、Schemas

1、Creating a Schema

使用CREATE SCHEMA命令来创建一个新的模式，你可以给这个模式命名为你选择的任何名字。例如：
```
CREATE SCHEMA myschema;
```
在一个模式中创建或访问对象时，需要写一个由模式名和表名组成的限定名，两者之间用点号（.）分隔。例如：
```
schema.table
```
这种写法在任何需要表名的地方都适用，包括表的修改命令和数据访问命令。

2、The Public Schema

在之前的部分中，创建表时如果没有指定模式名，默认情况下，这些表（以及其他对象）会自动放入名为“public”的模式中。因此，每个新数据库都包含一个这样的模式。因此，以下两种方式是等价的：
```
CREATE TABLE products (...);
```
和
```
CREATE TABLE public.products (...);
```
“public”模式是默认创建的，它提供了一种方便的方式，使得用户不需要明确指定模式名就能创建和访问对象。

3、The Schema Search Path

限定名（即包含模式名的名称，如schema.table）写起来可能比较繁琐，而且通常最好不要在应用程序中硬编码特定的模式名。因此，表通常通过非限定名（即只有表名，没有模式名）来引用。系统通过遵循一个搜索路径来确定指的是哪个表。搜索路径是一系列模式的列表，系统会按照这个列表的顺序来搜索匹配的表。搜索路径中的第一个匹配的表被认为是要操作的表。
可以使用以下命令显示当前的搜索路径：
```
SHOW search_path;
```
默认设置会返回：
```
"$user", public
```
这表示首先搜索与当前用户同名的模式（如果存在），如果没有找到，则搜索public模式。
如果要在搜索路径中添加新的模式，可以使用：
```
SET search_path TO myschema,public;
```
这样，在未指定模式的情况下访问表时，首先会在myschema模式中查找，如果没有找到，再在public模式中查找。
搜索路径的设置影响着无模式限定时对象的查找、新对象的默认创建位置等。通过合理设置搜索路径，可以提高数据库操作的灵活性和便捷性。

4、Schemas and Privileges

默认情况下，用户无法访问他们不拥有的模式中的任何对象。为了允许用户访问模式中的对象，模式的所有者必须授予用户在该模式上的USAGE权限。默认情况下，所有用户都有对public模式的USAGE权限。
用户也可以被允许在其他人的模式中创建对象。为了实现这一点，需要在模式上授予CREATE权限。在从PostgreSQL 14或更早版本升级的数据库中，所有用户默认都有对public模式的CREATE权限。某些使用模式可能会要求撤销这个权限：
```
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```
这里的第一个“public”指的是模式，第二个“PUBLIC”意味着“每个用户”。在第一个场合中它是一个标识符，在第二个场合中它是一个关键字，因此大小写不同；这回顾了第4.1.1节中的指导原则。

5、The System Catalog Schema

除了public模式和用户创建的模式之外，每个数据库都包含一个名为pg_catalog的模式。这个模式包含了系统表以及所有内建的数据类型、函数和操作符。pg_catalog模式总是被隐式地包含在搜索路径中。如果在搜索路径中没有显式地命名pg_catalog，那么在搜索路径的模式之前，系统会隐式地搜索pg_catalog。这确保了内建的名称总是可以找到。
由于系统表的名称以pg_开头，最好避免使用这个前缀来命名你自己的表，以确保将来版本的PostgreSQL引入的新系统表不会与你的表发生命名冲突。遵循这个约定可以避免潜在的名称冲突问题。

6、Usage Patterns

将普通用户限制在用户私有模式中：为了实现这个模式，首先确保没有模式具有公共的CREATE权限。然后，对于每个需要创建非临时对象的用户，创建一个与该用户同名的模式，例如CREATE SCHEMA alice AUTHORIZATION alice。这种模式是安全的，除非不受信任的用户是数据库所有者或者被授予了相关角色的ADMIN OPTION。在PostgreSQL 15及更高版本中，默认配置支持这种使用模式。
从默认搜索路径中移除public模式：通过修改postgresql.conf或者发出ALTER ROLE ALL SET search_path = "$user"命令，然后授予在public模式中创建对象的权限。只有使用限定名才能选择public模式的对象。如果在public模式中创建函数或扩展，使用第一个模式而不是这个。
保持默认搜索路径，并授予在public模式中创建对象的权限：所有用户都隐式地访问public模式。这模拟了没有模式可用的情况，为从非模式感知的世界平滑过渡提供了一种方式。然而，这从未是一个安全的模式。它只在数据库有单一用户或几个互相信任的用户时是可接受的。
为任何模式安装共享应用程序：将它们放入单独的模式中。记得授予其他用户适当的权限，以便他们能够访问这些对象。用户可以通过在名字中加上模式名来引用这些额外的对象，或者可以将额外的模式加入到他们的搜索路径中，根据他们的选择。

7、Portability

在SQL标准中，并不存在在同一模式中对象被不同用户拥有的概念。此外，一些实现不允许你创建与其所有者名称不同的模式。事实上，数据库系统仅实现标准中规定的基本模式支持时，模式和用户的概念几乎是等同的。因此，许多用户将限定名视为user_name.table_name。如果为每个用户创建一个每用户模式，PostgreSQL将以这种方式有效地运行。
SQL标准中也没有public模式的概念。为了最大程度地符合标准，你应该避免使用public模式。
当然，一些SQL数据库系统可能根本不实现模式，或者通过允许（可能有限的）跨数据库访问来提供命名空间支持。如果你需要与这些系统一起工作，那么通过不使用模式来实现最大的可移植性将是最佳方案。

八、Inheritance

表继承的实现：PostgreSQL实现了表继承，这可以作为数据库设计师的一个有用工具。SQL:1999及后续版本定义了类型继承特性，这与这里描述的特性在许多方面不同。
继承的示例：举了一个构建城市数据模型的例子。每个州有许多城市，但只有一个首府。想要快速检索任何特定州的首府城市。通过创建两个表，一个用于州首府，另一个用于非首府城市，可以解决这个问题。然而，当我们想要查询关于城市的数据时，不管它是否是首府，继承功能可以帮助解决这个问题。我们定义capitals表继承自cities表，capitals表继承了cities表的所有列，并且州首府还有一个额外的列state。
继承的查询：在PostgreSQL中，一个表可以从零个或多个其他表继承，查询可以引用表中的所有行或表及其所有后代表中的所有行。后者是默认行为。例如，查询可以找到位于超过500英尺高度的所有城市，包括州首府。
继承的限制：继承并不会自动将INSERT或COPY命令的数据传播到其他表中的继承层次结构。在我们的示例中，尝试向cities表插入一行数据，并希望数据能自动路由到capitals表，但这不会发生：INSERT总是插入到明确指定的表中。
约束和继承：所有父表上的检查约束和非空约束默认都被子表继承，除非明确指定了NO INHERIT子句。其他类型的约束（如唯一约束、主键约束和外键约束）不会被继承。
多重继承：一个表可以从多个父表继承，这种情况下，它具有所有父表定义的列的联合。如果相同的列名出现在多个父表中，或者在父表和子表的定义中，则这些列会被“合并”，以便子表中只有一个这样的列。
动态添加和移除继承关系：表继承关系通常在子表创建时通过INHERITS子句建立。或者，已经以兼容方式定义的表可以通过ALTER TABLE的INHERIT变体添加新的父子关系。类似地，可以使用ALTER TABLE的NO INHERIT变体从子表中移除继承关系。动态添加和移除继承链接在使用表继承进行表分区时很有用。

九、Table Partitioning

1、Overview

分区的定义：分区是将逻辑上看似一个大表的数据分割成多个物理较小片段的过程。分区可以带来几个好处，比如在某些情况下显著提高查询性能，特别是当绝大部分被频繁访问的行都在一个或少数几个分区中时。
分区的好处：
- 在查询或更新访问单个分区的大部分数据时，使用分区扫描而不是索引可以提高性能，因为这样可以避免全表的随机访问读取。
- 可以通过添加或删除分区来快速进行批量加载和删除，这比传统的数据操作更快。
- 不常用的数据可以迁移到更便宜、更慢的存储介质上。
分区的类型：PostgreSQL支持以下形式的分区：
- 范围分区（Range Partitioning）：按照键值的范围将表分区，不同分区的键值范围不重叠。
- 列表分区（List Partitioning）：通过明确列出哪些键值出现在每个分区中来进行分区。
- 哈希分区（Hash Partitioning）：通过为每个分区指定取模值和余数来进行分区。每个分区将包含键值经哈希后除以指定模数所得余数与指定余数相匹配的行。

2、Declarative Partitioning

PostgreSQL 允许您声明一个表被划分为分区的功能。被划分的表被称为分区表。声明包括上述提到的分区方法（范围分区、列表分区、哈希分区），以及用作分区键的列或表达式列表。
分区表本身是一个“虚拟”表，没有自己的存储。存储属于分区，这些分区是与分区表相关联的普通表。每个分区存储由其分区边界定义的数据子集。插入到分区表中的所有行将根据分区键列的值被路由到适当的分区之一。如果行的分区键更新导致它不再满足其原始分区的分区边界，则会将其移动到不同的分区。
分区本身可以定义为分区表，从而产生子分区。尽管所有分区必须与其分区父表具有相同的列，但分区可以拥有自己的索引、约束和默认值，与其他分区区别开来。有关创建分区表和分区的更多详细信息，请参见 CREATE TABLE。
不可能将常规表转换为分区表，反之亦然。然而，可以将现有的常规表或分区表作为分区表的一个分区添加，或者将一个分区从分区表中移除，将其变成一个独立的表；这可以简化并加速许多维护过程。有关 ATTACH PARTITION 和 DETACH PARTITION 子命令的更多信息，请参见 ALTER TABLE。
分区还可以是外部表，尽管需要格外小心，因为用户需要负责外部表的内容满足分区规则。还有一些其他限制。

假设我们正在为一家大型冰淇淋公司构建数据库。该公司每天测量最高温度，并记录每个地区的冰淇淋销量。从概念上讲，我们希望有一个表如下所示：


CREATE TABLE measurement (
    city_id   int not null,
    logdate   date not null,
    peaktemp  int,
    unitsales int

);

我们知道大多数查询将只访问最近一周、一个月或一个季度的数据，因为该表的主要用途是为管理层准备在线报告。为了减少需要存储的旧数据量，我们决定只保留最近3年的数据。每个月初，我们将删除最旧一个月的数据。在这种情况下，我们可以使用分区来帮助我们满足测量表的所有不同要求。
要在这种情况下使用声明性分区，可以按照以下步骤操作：

通过指定 PARTITION BY 子句（包括分区方法（在此例中为 RANGE）和用作分区键的列列表）来创建 measurement 表作为分区表。


CREATE TABLE measurement (
    city_id   int not null,
    logdate   date not null,
    peaktemp  int,
    unitsales int

) PARTITION BY RANGE (logdate);

创建分区。每个分区的定义必须指定与父级的分区方法和分区键相对应的边界。注意，指定边界使得新分区的值与一个或多个现有分区的值重叠会导致错误。分区一旦创建，就是普通的 PostgreSQL 表（或可能是外部表）。
例如，为了匹配每次删除一个月数据的要求，每个分区应该包含一个月的数据。所以命令可能看起来像这样：


CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM (2006-02-01) TO (2006-03-01);
CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM (2006-03-01) TO (2006-04-01);
...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM (2007-11-01) TO (2007-12-01);
CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM (2007-12-01) TO (2008-01-01) TABLESPACE fasttablespace;
CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM (2008-01-01) TO (2008-02-01) WITH (parallel_workers = 4) TABLESPACE fasttablespace;

如果您希望实现子分区，再次在用于创建个别分区的命令中指定 PARTITION BY 子句，例如：


CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM (2006-02-01) TO (2006-03-01) PARTITION BY RANGE (peaktemp);

创建 measurement_y2006m02 的分区后，插入到 measurement 表并映射到 measurement_y2006m02 的任何数据（或直接插入到 measurement_y2006m02，只要满足其分区约束）将根据 peaktemp 列进一步重定向到其分区之一。分区键可以与父分区的分区键重叠，尽管在指定子分区的边界时应当小心，以确保它接受的数据集是分区自己边界允许的子集；系统不会检查是否真的是这种情况。

在分区表的关键列上创建索引,以及任何你可能想要的其他索引。(关键字索引不是严格必需的,但在大多数情况下都很有帮助。)这将自动在每个分区上创建匹配的索引,你以后创建或附加的任何分区也将具有这样的索引。在分区表上声明的索引或唯一约束与分区表本身一样是"虚拟的":实际数据位于各个分区表上的子索引中。
确保在 postgresql.conf 中没有禁用 enable_partition_pruning 配置参数。如果禁用了,查询将不会按预期进行优化。

2、Partition Maintenance

当最初定义表时建立的分区集合通常不是固定不变的。通常，需要移除包含旧数据的分区，并定期为新数据添加新分区。分区的一个重要优点是，它允许通过操作分区结构来几乎瞬间执行这种本来很痛苦的任务，而不是物理地移动大量数据。

移除旧数据的最简单方法是直接删除不再需要的分区：


DROP TABLE measurement_y2006m02;

这可以非常快速地删除数百万条记录，因为它不需要逐条删除每条记录。需要注意的是，上述命令需要在父表上获取一个 ACCESS EXCLUSIVE 锁。

另一个通常更可取的选项是从分区表中移除分区，但保留对它作为单独表的访问权。这有两种形式：


ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

这些操作允许在最终删除数据之前对其进行进一步操作。例如，这通常是使用 COPY、pg_dump 或类似工具备份数据的好时机。也可能是聚合数据到更小格式、执行其他数据操作或运行报告的好时机。第一种形式的命令需要在父表上获取一个 ACCESS EXCLUSIVE 锁。在第二种形式中添加 CONCURRENTLY 限定符，允许分离操作仅需要在父表上获取 SHARE UPDATE EXCLUSIVE 锁，但请参阅 ALTER TABLE … DETACH PARTITION 了解限制的详细信息。

类似地，我们可以添加新分区来处理新数据。我们可以在分区表中创建一个空分区，就像上面创建原始分区一样：


CREATE TABLE measurement_y2008m02 PARTITION OF measurement 
FOR VALUES FROM (2008-02-01) TO (2008-03-01) 
TABLESPACE fasttablespace;

作为替代方案，有时在分区结构外创建新表然后稍后将其作为分区附加更方便。这允许在数据出现在分区表中之前加载、检查和转换新数据。此外，ATTACH PARTITION 操作仅需要在分区表上获取 SHARE UPDATE EXCLUSIVE 锁，与 CREATE TABLE … PARTITION OF 所需的 ACCESS EXCLUSIVE 锁相比，它对分区表上的并发操作更友好。可以使用 CREATE TABLE … LIKE 选项来避免繁琐地重复父表的定义：


CREATE TABLE measurement_y2008m02

(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS) 
TABLESPACE fasttablespace;
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE 2008-02-01 AND logdate < DATE 2008-03-01 );
\copy measurement_y2008m02 from measurement_y2008m02 -- 可能还有一些其他数据准备工作

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM (2008-02-01) TO (2008-03-01);

在执行 ATTACH PARTITION 命令之前，建议在要附加的表上创建一个 CHECK 约束，以匹配预期的分区约束，如上所示。这样，系统就可以跳过否则需要验证隐式分区约束的扫描。如果没有 CHECK 约束，将在持有该分区的 ACCESS EXCLUSIVE 锁的情况下扫描表以验证分区约束。建议在 ATTACH PARTITION 完成后删除现在多余的 CHECK 约束。如果附加的表本身是一个分区表，那么它的每个子分区将被递归锁定和扫描，直到遇到合适的 CHECK 约束或到达叶子分区为止。

3、Limitations

要在分区表上创建唯一或主键约束，分区键中不能包含任何表达式或函数调用，并且约束的列必须包含所有分区键列。这个限制的存在是因为构成约束的各个索引只能直接在它们自己的分区内强制执行唯一性；因此，分区结构本身必须保证不同分区间没有重复项。
无法在整个分区表上创建跨分区的排他约束。只能在每个叶子分区上单独设置这样的约束。再次，这个限制源自于无法强制执行跨分区的限制。
在 INSERT 操作中的 BEFORE ROW 触发器不能改变新行的最终目的分区。
不允许在同一分区树中混合使用临时和永久关系。因此，如果分区表是永久的，则其分区也必须是永久的，反之亦然。在使用临时关系时，分区树的所有成员都必须来自同一个会话。
尽管分区和它们的分区表使用继承的方式链接在一起，但并不是所有通用继承的特性都能与声明性分区的表或分区一起使用。特别是，分区不能有除了它是分区表的分区之外的任何父表，也不能有一个表同时继承自分区表和常规表。这意味着分区表及其分区从不与常规表共享继承层次结构。
由于分区表及其分区的层次结构仍然是一个继承层次结构，因此 tableoid 和所有正常的继承规则如第5.10节所述适用，但有一些例外：
分区不能有不在父表中出现的列。使用 CREATE TABLE 创建分区时，不可能指定列，也不可能使用 ALTER TABLE 在事后向分区添加列。只有当它们的列与父表完全匹配时，表才能作为分区与 ALTER TABLE … ATTACH PARTITION 添加。
分区表的 CHECK 和 NOT NULL 约束总是被所有分区继承。在分区表上创建标记为 NO INHERIT 的 CHECK 约束是不允许的。如果父表中存在相同约束，则不能在分区的列上删除 NOT NULL 约束。
使用 ONLY 在只有分区表时添加或删除约束是支持的，只要没有分区存在。一旦存在分区，使用 ONLY 会导致除 UNIQUE 和 PRIMARY KEY 之外的任何约束出现错误。相反，可以在分区本身上添加（如果它们不在父表中）和删除约束。
由于分区表本身没有任何数据，因此尝试使用 TRUNCATE ONLY 在分区表上总是返回错误。

3、Partitioning Using Inheritance

使用表继承实现分区的方法。虽然内置的声明性分区适用于大多数常见用例，但在某些情况下，使用表继承实现的更灵活的分区方法可能会很有用。表继承允许实现一些声明性分区不支持的特性，例如：

对于声明性分区，分区必须与分区表有完全相同的列集合，而使用表继承时，子表可以有不在父表中的额外列。
表继承允许多重继承。
声明性分区只支持范围分区、列表分区和哈希分区，而表继承允许用户选择的方式来划分数据。（然而，如果约束排除无法有效地剪枝子表，查询性能可能很差。）

1、Example

创建“根”表，所有的“子”表将从这个表继承。这个表不包含任何数据。除非你打算让所有子表都等同地应用这些约束，否则不要在这个表上定义任何检查约束。在这个例子中，根表是最初定义的 measurement 表。


CREATE TABLE measurement (
    city_id   int not null,
    logdate   date not null,
    peaktemp  int,
    unitsales int

);

创建多个从根表继承的“子”表。通常，这些表不会向从根表继承的集合中添加任何列。就像声明性分区一样，这些表在所有方面都是普通的 PostgreSQL 表（或外部表）。
为子表添加不重叠的表约束，以定义每个表允许的键值。典型的例子可能是：


CHECK ( x = 1 )
CHECK ( county IN ( Oxfordshire, Buckinghamshire, Warwickshire ))
CHECK ( outletID >= 100 AND outletID < 200 )

确保约束保证不同子表中允许的键值不重叠。设置范围约束时，应该这样定义范围：


CREATE TABLE measurement_y2006m02 (CHECK ( logdate >= DATE 2006-02-01 AND logdate < DATE 2006-03-01 )) INHERITS (measurement);

为每个子表创建键列的索引，以及可能需要的任何其他索引。
如果想要应用能够说 INSERT INTO measurement … 并将数据重定向到适当的子表，我们可以通过在根表上附加合适的触发器函数来实现这一点。
确保在 postgresql.conf 中没有禁用 constraint_exclusion 配置参数;否则可能会不必要地访问子表

2、Maintenance for Inheritance Partitioning

使用继承实现的分区时的维护工作。这包括如何添加新的子表（分区）、移除旧的子表（分区），以及如何管理继承分区的数据。

**移除旧数据：**要快速移除旧数据，只需简单地删除不再需要的子表即可：


DROP TABLE measurement_y2006m02;

这个操作会立即删除该子表及其所有数据，这比逐条删除数据要快得多。

**从继承层次结构中移除子表但保留作为独立表的访问权：**如果你想保留子表的数据但不希望它作为查询的一部分，可以使用 NO INHERIT 选项将子表从继承层次结构中移除：


ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

这样，子表 measurement_y2006m02 不再被视为 measurement 表的一部分，但仍然存在于数据库中，可以独立访问。

**添加新的子表来处理新数据：**当需要处理新数据时，可以创建一个空的子表，就像最初创建子表一样，并让它继承自根表：


CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE 2008-02-01 AND logdate < DATE 2008-03-01 )
) INHERITS (measurement);

这个新表会自动继承根表的所有属性，但有自己的数据和约束。

**在将子表添加到表继承层次结构之前进行数据加载、检查和转换：**有时，在子表成为查询的一部分之前，你可能希望先在子表中加载和处理数据。这可以通过先创建子表，然后通过执行数据加载和转换操作来完成，最后使用 INHERIT 选项将子表添加到继承层次结构中：


CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE 2008-02-01 AND logdate < DATE 2008-03-01 );
-- 进行数据加载和其他准备工作

ALTER TABLE measurement_y2008m02 INHERIT measurement;

使用继承实现的分区需要更多的手动管理工作，包括创建和维护子表、约束和触发器。虽然这提供了更高的灵活性，但也增加了复杂性。管理员需要仔细规划和管理分区策略，以确保数据的组织和访问效率。

3、Caveats

**检查约束的互斥性：**PostgreSQL 不会自动验证所有子表的 CHECK 约束是否互不重叠。手动确保这些约束之间没有重叠是很重要的，因为重叠的约束可能导致数据插入不正确的分区。为了安全起见，生成子表和相关对象的代码比手动编写每个部分更可靠。
**索引和外键约束的局限性：**索引和外键约束仅适用于单个表，而不适用于其继承的子表。因此，这些约束在使用继承实现分区时具有一些需要注意的局限性。
**更新可能导致行移动到其他分区的情况：**这里假设行的键列值（即使其作为分区键的一部分）不会更改，或者至少不会更改到需要将其移动到另一个分区的程度。如果尝试进行这样的更新，由于 CHECK 约束，操作会失败。如果需要处理这种情况，可以在子表上使用适当的更新触发器，但这会使结构管理变得更加复杂。
**手动 VACUUM 或 ANALYZE 命令的使用：**不要忘记需要在每个子表上单独运行这些命令。例如，对根表执行 ANALYZE measurement; 只会处理根表本身，不会涉及任何子表。
**INSERT 语句中的 ON CONFLICT 子句的使用限制：**使用 ON CONFLICT 子句的 INSERT 语句可能不会按预期工作，因为 ON CONFLICT 动作仅在指定目标关系上的唯一违规情况下才会采取，而不考虑其子关系。
**触发器或规则的需要：**除非应用程序明确了解分区方案，否则可能需要触发器或规则来将行路由到期望的子表。触发器可能编写起来较为复杂，并且比声明性分区内部执行的元组路由要慢得多。

4、Partition Pruning

分区剪枝，这是一种查询优化技术，用于改善声明性分区表的性能。分区剪枝通过在查询计划阶段排除（剪枝）不需要扫描的分区来提高查询效率，从而只扫描可能包含符合查询条件的数据的分区。
例如，如果执行一个查询，该查询的 WHERE 子句限定条件只能被某些分区中的数据满足，那么查询规划器将检查每个分区的定义，并证明某些分区不需要被扫描，因为它们不可能包含满足查询条件的行。当规划器能够证明这一点时，它就会从查询计划中排除（剪枝）这些分区。
使用 EXPLAIN 命令和配置参数 enable_partition_pruning，可以展示分区剪枝启用与未启用时查询计划之间的区别。一个典型的未优化计划可能会扫描 measurement 表的每个分区，而启用分区剪枝后，计划会显著更简单、成本更低，因为它只扫描满足 WHERE 子句条件的分区。
分区剪枝的主要优点是可以显著减少查询需要处理的数据量，从而提高查询性能。它特别适用于那些数据被分布在多个分区中，并且查询经常只关注特定时间段或数据范围的情况。
分区剪枝不仅可以在查询规划阶段进行，还可以在查询执行期间进行。这意味着如果查询中包含的某些条件或参数在规划阶段不可知，但在执行阶段可以确定，则在执行阶段也可以进行分区剪枝。这在使用参数化查询或查询中包含从子查询中获取的值时特别有用。
通过配置参数 enable_partition_pruning 可以启用或禁用分区剪枝功能。默认情况下，这个功能是启用的，因为它可以为许多查询带来性能提升。

5、Partitioning and Constraint Exclusion

约束排除是一种类似于分区剪枝的查询优化技术，主要用于通过遗留的继承方式实现的分区，但也可以用于其他目的，包括声明性分区。约束排除利用每个表的 CHECK 约束来优化查询，而分区剪枝则利用表的分区边界，后者仅存在于声明性分区中。一个重要的区别是，约束排除仅在查询规划时应用，而不会在查询执行时尝试移除分区。
约束排除使用 CHECK 约束的事实，虽然使其相比分区剪枝慢一些，但有时可以作为一个优势：因为即使在声明性分区的表上定义了 CHECK 约束，除了它们的内部分区边界之外，约束排除也可能能够从查询计划中排除额外的分区。
约束排除的默认设置（也是推荐设置）不是开启（on）也不是关闭（off），而是一个中间设置称为 partition，这使得技术仅应用于可能正在操作继承分区表的查询。开启设置（on）会使规划器在所有查询中检查 CHECK 约束，即使是那些不太可能从中受益的简单查询。
需要注意的一些注意事项包括：

约束排除仅在查询规划期间应用，与可以在查询执行期间应用的分区剪枝不同。
约束排除只在查询的 WHERE 子句包含常量（或外部提供的参数）时工作。例如，与非不可变函数（如 CURRENT_TIMESTAMP）的比较不能被优化，因为规划器无法知道函数的值可能落入哪个子表。
保持分区约束简单，否则规划器可能无法证明不需要访问子表。分区约束应仅包含分区列与常量的比较，使用 B-tree 索引支持的操作符，因为只有 B-tree 索引支持的列才被允许在分区键中使用。
检查所有父表的子表的约束会增加查询规划时间，因此使用遗留的继承方式实现的分区在子表数量较多时可能会导致性能问题。因此，建议使用遗留继承方式的分区表的子表数量不要超过几百个。

6、Best Practices for Declarative Partitioning

关于声明性分区的最佳实践建议，以帮助设计和管理分区表，以提高查询计划和执行的性能，同时避免可能的性能下降。

**选择分区键：**选择作为分区依据的列或列集是关键设计决策之一。通常，最好的选择是那些最常出现在查询的 WHERE 子句中的列。符合分区边界约束的 WHERE 子句可以用于剪枝不需要的分区。然而，PRIMARY KEY 或 UNIQUE 约束的要求也可能影响分区键的选择。此外，还应考虑数据的移除方式，设计分区策略使得可以一次性快速移除整个分区的数据。
**确定分区的目标数量：**选择将表分割成多少个分区也是一个重要的决策。分区数量不足可能导致索引仍然过大，数据局部性差，缓存命中率低。然而，过多的分区会导致查询计划时间更长和执行期间的内存消耗更高。未来可能发生的变化，如客户数量增长，也应该在选择分区策略时考虑。
**子分区的使用：**对于预计会比其他分区更大的分区，子分区可以用来进一步划分数据。使用带有多个列的范围分区也是一个选项。但是，这两种方法都可能导致分区数量过多，因此需要谨慎使用。
**查询计划和执行的开销：**查询规划器通常能够很好地处理数千个分区的分区层次结构，前提是典型的查询能让规划器剪枝大部分分区。当剩余的分区数量较多时，规划时间会变长，内存消耗也会增加。此外，大量分区的使用可能导致服务器随时间消耗大量内存，特别是如果许多会话接触到大量分区。
**数据仓库与OLTP工作负载：**对于数据仓库类型的工作负载，使用比OLTP类型工作负载更多的分区可能是有意义的。在数据仓库中，查询计划时间通常不像查询执行时间那么关键。无论是哪种类型的工作负载，在早期做出正确的决策都很重要，因为重新分区大量数据可能非常缓慢。
这些最佳实践建议强调了在设计和实施声明性分区策略时需要考虑的各种因素，以确保数据的有效组织和高效访问。

十、Foreign Data

PostgreSQL 如何实现 SQL/MED（管理外部数据）规范的一部分，允许您使用常规 SQL 查询访问存储在 PostgreSQL 外部的数据。这些外部数据被称为外部数据。
访问外部数据需要借助外部数据封装器（Foreign Data Wrapper，简称 FDW）。外部数据封装器是一种库，它能够与外部数据源通信，隐藏连接到数据源和从中获取数据的细节。PostgreSQL 提供了一些作为 contrib 模块的外部数据封装器；详见附录 F。也可能存在第三方提供的其他类型的外部数据封装器。如果现有的外部数据封装器不满足您的需求，您还可以自己编写；详见第 59 章。
要访问外部数据，您需要创建一个外部服务器对象，该对象根据其支持的外部数据封装器使用的选项集定义如何连接到特定的外部数据源。然后，您需要创建一个或多个外部表，这些表定义了远程数据的结构。外部表可以像普通表一样在查询中使用，但外部表在 PostgreSQL 服务器中没有存储。每当使用它时，PostgreSQL 会要求外部数据封装器从外部源获取数据，或在更新命令的情况下，将数据传输到外部源。
访问远程数据可能需要对外部数据源进行身份验证。这些信息可以通过用户映射提供，用户映射可以根据当前的 PostgreSQL 角色提供额外的数据，如用户名和密码。
有关更多信息，请参见 CREATE FOREIGN DATA WRAPPER、CREATE SERVER、CREATE USER MAPPING、CREATE FOREIGN TABLE 和 IMPORT FOREIGN SCHEMA。

十一、Other Database Objects

数据库中除了表之外还存在的其他对象类型。这些对象可以提高数据使用和管理的效率或便利性。虽然本章没有详细讨论这些对象，但这里列出了一些，以便您了解数据库中可能包含的其他可能性：

视图（Views）：视图是基于 SQL 查询的虚拟表。它们提供了一种方式来封装复杂的查询，让您可以像访问表一样访问这些查询的结果。
函数、过程和操作符（Functions, Procedures, and Operators）：这些是可以执行特定操作的程序，例如，函数可以用来计算数据、处理字符串等。过程（在某些数据库系统中称为存储过程）类似于函数，但通常用于执行包含多个步骤的操作序列。
数据类型和域（Data Types and Domains）：数据库支持多种数据类型，例如整数、浮点数、字符串等。域是基于现有数据类型但附加了一些约束的自定义数据类型。
触发器和重写规则（Triggers and Rewrite Rules）：触发器是数据库对象，当特定事件（如插入、更新或删除）发生在表上时，它会自动执行定义好的操作。重写规则允许您定义在执行某些类型的查询时应用的转换规则。
这些对象的详细信息出现在手册的第五部分。它们提供了强大的工具来优化和定制数据库操作，使数据库能够更好地满足特定应用程序的需求。

十二、Dependency Tracking

当创建复杂的数据库结构，涉及许多表、外键约束、视图、触发器、函数等时，您隐式地创建了对象之间的依赖网。例如，一个带有外键约束的表依赖于它引用的表。
为了确保整个数据库结构的完整性，PostgreSQL 会确保您不能删除其他对象仍然依赖的对象。比如，尝试删除 products 表，而 orders 表依赖于它，将会导致错误信息。错误信息中包含一个有用的提示：如果您不想逐个删除所有依赖对象，可以运行 DROP ... CASCADE 命令，这样所有依赖的对象以及依赖于这些对象的对象都会被递归地移除。在这种情况下，不会移除 orders 表，只会移除外键约束。如果想要检查 DROP ... CASCADE 将会做什么，可以运行没有 CASCADE 的 DROP 命令并阅读 DETAIL 输出。
几乎所有的 DROP 命令在 PostgreSQL 中都支持指定 CASCADE。当然，可依赖性的性质会随对象的类型而变化。您也可以写 RESTRICT 来代替 CASCADE 来获得默认行为，即阻止删除其他对象依赖的对象。
根据 SQL 标准，DROP 命令中指定 RESTRICT 或 CASCADE 是必需的。尽管没有数据库系统实际强制执行这一规则，但默认行为是 RESTRICT 还是 CASCADE 在不同的系统中可能会有所不同。
如果一个 DROP 命令列出了多个对象，只有当存在指定组之外的依赖时，才需要 CASCADE。例如，当说 DROP TABLE tab1, tab2 时，如果 tab1 从 tab2 引用一个外键，这并不意味着需要 CASCADE 才能成功。
对于用户定义的函数或过程，如果其主体定义为字符串字面量，PostgreSQL 会跟踪与函数的外部可见属性相关的依赖，如其参数和结果类型，但不会跟踪只能通过检查函数主体才能知道的依赖。例如，尽管 PostgreSQL 会意识到 get_color_note 函数依赖于 rainbow 类型，但它不会将 get_color_note 视为依赖于 my_colors 表，因此如果表被删除，函数不会被移除。尽管这种方法有其缺点，但也有其优点。如果表缺失，函数在某种意义上仍然是有效的，尽管执行它会导致错误；创建一个同名的新表将允许函数再次工作。

墨力计划

最后修改时间：2024-04-07 17:43:31

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者