5、PostgreSQL之数据定义

PostgreSQL之数据定义

1、表基础

关系型数据库中的一个表非常像纸上的一张表：它由行和列组成。列的数量和顺序是固定的，并且每一列拥有一个名字。行的数目是变化的，它反映了在一个给定时刻表中存储的数据量。

每一列都有一个数据类型。数据类型约束着一组可以分配给列的可能值，并且它为列中存储的数据赋予了语义，这样它可以用于计算。例如，一个被声明为数字类型的列将不会接受任何文本串，而存储在这样一列中的数据可以用来进行数学计算。反过来，一个被声明为字符串类型的列将接受几乎任何一种的数据，它可以进行如字符串连接的操作但不允许进行数学计算。

1.1、建表语法

CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);

1.2、删表语法

DROP TABLE my_first_table;
DROP TABLE products;

2、默认值

**一个列可以被分配一个默认值。当一个新行被创建且没有为某些列指定值时，这些列将会被它们相应的默认值填充。**如果没有显式指定默认值，则默认值是空值。

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

默认值可以是一个表达式，它将在任何需要插入默认值的时候被实时计算（***不***是表创建时）。一个常见的例子是为一个timestamp列指定默认值为CURRENT_TIMESTAMP，这样它将得到行被插入时的时间。（就是当把这条数据插入时的时间戳插入）

3、约束

SQL允许我们在列和表上定义约束。约束让我们能够根据我们的愿望来控制表中的数据。如果一个用户试图在一个列中保存违反一个约束的数据，一个错误会被抛出。

3.1、检查约束（CHECK）

检查约束是最简单的约束类型。它允许我们指定一个特定列中的值必须要满足一个布尔表达式。

举个例子

为了要求产品的价格为正值，那么我们可以这么写

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

约束的定义的默认值一样也是跟在数据类型的后面，默认值和约束之间的顺序无所谓。

当然，我们还可以给约束起一个名字，当我们要修改这个约束时会很方便，同时这也会使得错误更加的清晰明了。

要指定一个约束的名字，需要在约束标识符之前使用关键字CONSTRAINT，然后在写约束的名字。

语法为：

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

当然，一个检查约束是可以引用多个列的。

举个例子

例如我们存储一个普通价格和一个打折后的价格，而我们希望保证打折后的价格低于普通价格：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_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)
);

或者是：

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

3.2、非空约束（NOT NULL）

非空约束目的就是让这个列中不能有空值。

语法如下：

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

非空约束应该是被写成列约束的。

你可能会说，写一个检查约束CHECK (column_name IS NOT NULL)同样是可以的。是的没错，但是在PostgreSQL中非空约束更高效。

当然，一个列是可以写多个约束的，而且约束是没有顺序的。同时非空约束是不能命名的。

3.3、唯一约束（UNIQUE）

唯一约束保证在一列中或者一组列中保存的数据在表中所有行间是唯一的。

写成一个列约束的语法：

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

写成一个表约束的语法是：

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

要为一组列定义一个唯一约束，把它写作一个表级约束，列名用逗号分隔：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

这指定这些列的组合值在整个表的范围内是唯一的，但其中任意一列的值并不需要是唯一的。

当然，我们可以给唯一约束命名：

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

注意：在PostgreSQL中即便存在一个唯一约束，也可以存储多个在至少一个被约束列中包含空值的行。

3.4、主键

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。

写法：

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

主键也可以包含多于一个列，其语法和唯一约束相似：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

**一个表最多只能有一个主键（**可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。关系数据库理论要求每一个表都要有一个主键。但PostgreSQL中并未强制要求这一点。

3.5、外键（REFERENCES）

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。

举个例子

还是这个产品表：

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
);

这种情况下：订单表是引用表而产品表是被引用表。

上面的写法还可以简写为：

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

因为如果缺少列的列表，则被引用表的主键将被用作被引用列。

当然，外键约束同样可以命名。

3.6、排他约束

排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。语法是：

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

4、修改表

4.1、增加列

要增加一个列，可以使用这样的命令：

ALTER TABLE products ADD COLUMN description text;

新列将被默认值所填充（如果没有指定DEFAULT子句，则会填充空值）。

也可以同时为列定义约束，语法：

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

CREATE TABLE中关于一列的描述都可以应用在这里。记住不管怎样，默认值必须满足给定的约束，否则ADD将会失败。也可以先将新列正确地填充好，然后再增加约束。

4.2、移除列

为了移除一个列，使用如下的命令：

ALTER TABLE products DROP COLUMN description;

列中的数据将会消失。涉及到该列的表约束也会被移除。然而，如果该列被另一个表的外键所引用，PostgreSQL不会安静地移除该约束。我们可以通过增加CASCADE来授权移除任何依赖于被删除列的所有东西：

ALTER TABLE products DROP COLUMN description CASCADE;

4.3、增加约束

为了增加一个约束，可以使用表约束的语法，例如：

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

要增加一个不能写成表约束的非空约束，可使用语法：

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束

4.4、移除约束

为了移除一个约束首先需要知道它的名称。如果在创建时已经给它指定了名称，那么事情就变得很容易。否则约束的名称是由系统生成的，我们必须先找出这个名称。可以通过psql的命令：\d 表名 来查询。

ALTER TABLE products DROP CONSTRAINT some_name;

（如果处理的是自动生成的约束名称，如$2，别忘了用双引号使它变成一个合法的标识符。）

我们知道非空约束是没有名字的，那么我们可以使用以下命令来移除：

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

4.5、更改列的默认值

要为一个列设置一个新默认值，使用命令：

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

注意这不会影响任何表中已经存在的行，它只是为未来的INSERT命令改变了默认值。

要移除任何默认值，使用：

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这等同于将默认值设置为空值。相应的，试图删除一个未被定义的默认值并不会引发错误，因为默认值已经被隐式地设置为空值。

4.6、修改列的数据类型

为了将一个列转换为一种不同的数据类型，使用如下命令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

**只有当列中的每一个项都能通过一个隐式造型转换为新的类型时该操作才能成功。**如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。

PostgreSQL将尝试把列的默认值转换为新类型，其他涉及到该列的任何约束也是一样。但是这些转换可能失败或者产生奇特的结果。因此最好在修改类型之前先删除该列上所有的约束，然后在修改完类型后重新加上相应修改过的约束。

4.7、重命名列

要重命名一个列：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

4.8、重命名表

要重命名一个表：

ALTER TABLE products RENAME TO items;

5、权限

一旦一个对象被创建，它会被分配一个所有者。所有者通常是执行创建语句的角色。对于大部分类型的对象，初始状态下只有所有者（或者超级用户）能够对该对象做任何事情。为了允许其他角色使用它，必须分配权限。

权限的种类：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE以及USAGE。

要分配权限，可以使用GRANT命令。

举个例子

如果joe是一个已有角色，而accounts是一个已有表，更新该表的权限可以按如下方式授权：

将更新该表的权限赋予joe

GRANT UPDATE ON accounts TO joe;

用ALL取代特定权限会把与对象类型相关的所有权限全部授权。

为了撤销一个权限，使用REVOKE命令：

REVOKE ALL ON accounts FROM PUBLIC;

6、模式

模式类似于操作系统层的目录，但是模式不能嵌套。

了解即可，很多数据库是没有模式这个概念的

6.1、创建模式

要创建一个模式，可使用CREATE SCHEMA命令，并且给出选择的模式名称。例如：

CREATE SCHEMA myschema;

在一个模式中创建或访问对象，需要使用由模式名和表名构成的限定名，模式名和表名之间以点号分隔：

模式.表

如果要在一个新模式中创建一个表，可用：

CREATE TABLE myschema.mytable (
 ...
);

要删除一个为空的模式（其中的所有对象已经被删除），可用：

DROP SCHEMA myschema;

要删除一个模式以及其中包含的所有对象，可用：

DROP SCHEMA myschema CASCADE;

6.2、公共模式

在前面的小节中，我们创建的表都没有指定任何模式名称。默认情况下这些表（以及其他对象）会自动的被放入一个名为“public”的模式中。任何新数据库都包含这样一个模式。

例如：

CREATE TABLE products ( ... );

以及：

CREATE TABLE public.products ( ... );

6.3、模式搜索路径

pass

7、继承

PostgreSQL实现了表继承，这个之前提到过。

还是之前的例子：

查询将查找所有海拔高于500尺的城市的名称，包括州首府：

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

它将返回：

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845

在另一方面，下面的查询将找到海拔超过500尺且不是州首府的所有城市：

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953

这里的ONLY关键词指示查询只被应用于cities上，而其他在继承层次中位于cities之下的其他表都不会被该查询涉及。很多我们已经讨论过的命令（如SELECT、UPDATE和DELETE）都支持ONLY关键词。

我们也可以在表名后写上一个*来显式地将后代表包括在查询范围内：

SELECT name, altitude
    FROM cities*
    WHERE altitude > 500;

继承不会自动地将来自INSERT或COPY命令的数据传播到继承层次中的其他表中。

8、表分区

8.1、概述

划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处：

• 在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
• 当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
• 如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。 执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE 删除一个单独的分区都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
• 很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。

PostgreSQL为以下形式的分区提供了内置支持：

• 范围分区

  该表被分区到由键列或列集定义的“范围”中， 分配给不同分区的值范围之间没有重叠。例如，可以按日期范围进行分区， 也可以按特定业务对象的标识符范围进行分区。

• 列表分区

  表通过明确列出每个分区中出现的键值进行分区。

如果您的应用程序需要使用上面未列出的其他形式的分区，则可以使用替代方法， 如继承和UNION ALL视图。这种方法提供了灵活性， 但没有内置声明式分区的一些性能优势。

就目前来说,我是完全用不到的,所以没有具体了解

9、数据库的其他对象

表是一个关系型数据库结构中的核心对象，因为它们承载了我们的数据。但是它们并不是数据库中的唯一一种对象。有很多其他种类的对象可以被创建来使得数据的使用和刮泥更加方便或高效。

例如:

• 视图
• 函数和操作符
• 数据类型和域
• 触发器和重写规则

10、依赖追踪

当我们创建一个涉及到很多具有外键约束、视图、触发器、函数等的表的复杂数据库结构时，我们隐式地创建了一张对象之间的依赖关系网。例如，具有一个外键约束的表依赖于它所引用的表。

为了保证整个数据库结构的完整性，PostgreSQL确保我们无法删除仍然被其他对象依赖的对象。例如，尝试删除之前提到的产品表会导致一个如下的错误消息，因为有订单表依赖于产品表：

DROP TABLE products;

ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint orders_product_no_fkey on table orders depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

该错误消息包含了一个有用的提示：如果我们不想一个一个去删除所有的依赖对象，我们可以执行：

DROP TABLE products CASCADE;

这样所有的依赖对象将被移除，同样依赖于它们的任何对象也会被递归删除。在这种情况下，订单表不会被移除，但是它的外键约束会被移除。

PostgreSQL中的几乎所有DROP命令都支持CASCADE。当然，其本质的区别随着对象的类型而不同。我们也可以用RESTRICT代替CASCADE来获得默认行为，它将阻止删除任何被其他对象依赖的对象。