PostgreSQL15——管理数据表

一、创建表

-- 语法
CREATE TABLE table_name(
	column_name data_type column_constraint,
	column_name data_type,
	...,
	table_constraint
);

-- 示例
create table departments(
	department_id integer not null primary key,
	department_name varchar(30) not null
);

PostgreSQL 支持 SQL 标准中的所有字段约束和表约束。其中，字段约束包括：

• NOT NULL：非空约束，该字段的值不能为空（NULL）。
• UNIQUE：唯一约束，该字段每一行的值不能重复。不过，PostgreSQL 允许该字段存在多个 NULL 值，并且将它们看作不同的值。需要注意的是 SQL 标准只允许 UNIQUE 字段中存在一个 NULL 值。
• PRIMARY KEY：主键约束，包含了 NOT NULL 约束和UNIQUE 约束。如果主键只包含一个字段，可以通过列级约束进行定义（参考上面的示例）；但是如果主键包含多个字段（复合主键）或者需要为主键指定一个自定义的名称，需要使用表级约束进行定义。
• REFERENCES：外键约束，字段中的值必需已经在另一个表中存在。外键用于定义两个表之间的参照完整性（referential integrity），例如，员工的部门编号字段必须是一个已经存在的部门。
• CHECK：检查约束，插入或更新数据时检查数据是否满足某个条件。例如，产品的价格必需大于零。
• DEFAULT：默认值，插入数据时，如果没有为这种列指定值，系统将会使用默认值代替。

表级约束和字段约束类似，只不过它是基于整个表定义的约束，还能够为约束指定自定义的名称。PostgreSQL 支持的表级约束包括：

• UNIQUE(column1, …)：唯一约束，括号中的字段值或字段值的组合必须唯一。
• PRIMARY KEY(column1, …)：主键约束，定义主键或者复合主键。
• REFERENCES：定义外键约束。
• CHECK：定义检查约束。

-- 以下示例创建员工表
create table employees(
	employee_id integer not null,
	first_name character varying(20),
	last_name character varying(25) not null,
	email character varying(25) not null,
	phone_number character varying(20),
	hire_date date not null,
	salary numeric(8,2),
	commission_pct numeric(2,2),
	manager_id integer,
	department_id integer,
	constraint emp_emp_id_pk primary key (employee_id),
	constraint emp_salary_min check (salary > 0),
	constraint emp_email_uk unique (email),
	constraint emp_dept_fk foreign key (department_id) references departments(department_id),
	constraint emp_manager_fk foreign key (manager_id) references employees (employee_id)
);

除了自己定义表的结构之外，PostgreSQL 还提供了另一个创建表的方法，就是通过一个查询的结果创建新表：

create table table_name
as query;

-- 或者

select ...
into new_table
from ...;

例如，我们可以基于 employees 复制出两个新的表：

create table emp1
as 
select *
from employees;

-- 或
select * 
into emp2
from employees;

1.1、模式搜索路径

在PostgreSQL 中，表属于某个模式（schema）。当我们创建表时，更完整的语法应该是：

CREATE TABLE schema_name.table_name
...

访问表的时候也是一样。但是我们在前面创建示例表的时候，并没有加上模式名称的限定。这里涉及到一个模式的搜索路径概念。

我们先看一下当前的搜索路径：

搜索路径是一个逗号分隔的模式名称。当我们使用表的时候，PostgreSQL 会依次在这些模式中进行查找，返回第一个匹配的表名；当我们创建一个新表时，如果没有指定模式名称，
PostgreSQL 会在第一个模式中进行创建。

第一个模式默认为当前用户名，如果不存在该模式，使用后面的公共模式（public）。

当前用户名为postgres，但是不存在名为 postgres 的模式，因此我们创建的表会位 public 模式中。

我们可以通过SET 命令修改默认的搜索路径：

SET search_path TO app,public;

此时，如果我们再创建新表而不指定模式名称时，默认会在模式 app 中创建。除了表之外，其他的模式对象，例如索引、函数、类型等等，也遵循相同的原则。

二、修改表

PostgreSQL 使ALTER TABLE 语句修改表的定义：

ALTER TABLE name action;

其中的 action 表示要执行的操作。常见的修改操作包括：

• 添加字段
• 删除字段
• 添加约束
• 删除约束
• 修改字段默认值
• 修改字段数据类型
• 重命名字段
• 重命名表

2.1、添加字段

添加字段与创建表时的字段选项相同，包含字段名称、字段类型以及可选的约束。为表添加一个字段的命令如下：

ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;

-- 假设我们已经创建了一个产品表 products
create table products (
	product_no integer primary key,
	name text,
	price numeric
);

-- 通过以下语句为产品表增加一个新的字段 description
alter table products add column description text;

对于表中已有的数据，新增加的列将会使用默认值进行填充；如果没有指定 DEFAULT 值，使用空值填充。

添加字段时还可以定义约束。不过需要注意的是，如果表中已经存在数据，新增字段的默认值有可能会违反指定的约束。例如：

-- 插入一行数据
insert into products(product_no, name, price)
values(1, 'Cheese', 9.99);

--新增字段notes
alter table products add column notes text not null;
-- 报错
ERROR:  column "notes" of relation "products" contains null values

以上语句出错的原因在于新增的字段 notes 存在非空约束，但是对于已有的数据该字段的值为空。解决的方法有两个：添加约束的同时指定一个默认值；添加字段时不指定约束，将所有数据的字段值手动填充（UPDATE）之后，再添加约束。

以下语句为新增的字段指定了一个默认值：

alter table products add column notes text default 'new product' not null;

select * from products;

2.2、删除字段

删除一个字段的语句如下：

ALTER TABLE table_name
DROP COLUMN column_name;

将产品表中的 notes 字段删除：

删 除 字 段后 ， 相应 的 数据 也 会自 动 删除 。 同时 ， 该字 段 上的 索 引或 约 束（products_description_check）也会同时被删除。但是，如果该字段被其他对象（例如外键引用、
视图、存储过程等）引用，无法直接删除。

由于departments 表的 department_id 是 employees 表的外键引用列，无法直接删除该字段。通过提示可以看出，在 DROP 的最后加上CASCADE 选项即可级联删除依赖的对象。

字段department_id 被删除，同时 employees 表中的外键也被级联删除。

2.3、添加约束

添加约束时通常使用表级约束语法：

ALTER TABLE	table_name ADD table_constraint;

以下是为产品表 products 增加约束的一些示例：

alter table products add constraint products_price_min CHECK (price > 0);

alter table products add constraint products_name_uk UNIQUE (name);

将产品表的 name 字段设置为非空：

alter table products alter name set not null;

2.4、删除约束

删除约束通常需要知道它的名称，可以通过 psql 工具的\d table_name 命令查看表的约束。

ALTER TABLE table_name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ];

RESTRICT 是默认值，如果存在其他依赖于该约束的对象，需要使用 CASCADE 执行级联删除。例如，外键约束依赖于被引用字段上的唯一约束或主键约束。

删除产品表 name 字段上的唯一约束：

删除非空约束也需要使用单独的语法：

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

以下语句将会删除产品表 name 字段上的非空约束：

2.5、修改字段默认值

如果想要为某个字段设置或者修改默认值，可以使用以下语句：

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;

为产品表的价格设置一个默认值：

alter table products alter column price set default 7.77;

同样，可以删除已有的默认值：

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

删除产品表中的价格默认值：

alter table products alter column price drop default;

2.6、修改字段数据类型

通常来说，可以将字段的数据类型修改为兼容的类型。

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

以下语句将产品表的 price 字段的类型修改为 numeric(10,2)：

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

因为已有的数据能够隐式转换为新的数据类型，上面的语句能够执行成功。如果无法执行隐式转换（例如将字符串‘1’转换为数字 1），可以使用 USING 执行显式转换。

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING expression;

我们先为产品表增加一个字符串类型的字段level，然后将其修改为整数类型：

alter table products add column level varchar(10);

2.7、重命名字段

使用以下语句可以修改表中字段的名称：

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

我们将产品表的字段 product_no 改名为 product_number：

alter table products
rename column product_no to product_number;

2.8、重命名表

如果需要修改表的名称，可以使用以下语句：

ALTER TABLE table_name
RENAME TO new_name;

例如，将产品表的名称改为 items 的命令如下：

alter table products rename to item;

三、删除表

删除表可以使用 DROP TABLE 语句：

DROP TABLE [ IF EXISTS ] name [ CASCADE | RESTRICT ];

其中，name 表示要删除的表；如果使用了 IF EXISTS，删除一个不存在的表不会产生错误，而是显示一个信息。如果被删除的表存在依赖于它的视图或外键约束，需要指定CASCADE 选项执行级联删除。