PostgreSQL 管理数据表（一）

创建表

在PostgreSQL中，使用CREATE TABLE语句创建一个新表：

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

建表语句说明

• 首先，table_name指定了新表的名称。
• 括号内是字段的定义，column_name是字段的名称，data_type是它的类型，column_constraint是可选的字段约束；多个字段使用逗号进行分隔。
• 最后，table_constraint是可选的表级约束

建表示例

CREATE TABLE departments
(
    department_id   INTEGER               NOT NULL PRIMARY KEY,
    department_name CHARACTER VARYING(30) NOT NULL
);

以上语句创建了一个新的部门表（departments）。它包含两个字段，部门编号（dept_id）是一个整数类型（INTEGER），字段的值不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）；部门编号（dept_name）是一个可变长度的字符串，也不允许为空。

PostgreSQL提供了丰富的内置数据类型，同时还允许用户自定义数据类型。最常见的基本数据类型包括：

• 字符类型，包括定长字符串CHAR(n)，变长字符串VARCHAR(n)，以及支持更大长度的字符串TEXT。
• 数字类型，包括整数类型SMALLINT、INTEGER、BIGINT，精确数字NUMERIC (p, s)，浮点数REAL、DOUBLE PRECISION。
• 时间类型，包括日期DATE、时间TIME、时间戳TIMESTAMP。

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，定义检查约束。

以下示例创建了员工表（employees）

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

• employee_id，员工编号，整数类型，主键（通过表级约束为主键指定了名称 emp_emp_id_pk）；
• first_name，名字，字符串；
• last_name，姓氏，字符串，不能为空；
• email，电子邮箱，字符串，不能为空，必须唯一（emp_email_uk）；
• phone_number，电话号码，字符串；
• hire_date，雇佣日期，日期类型，不能为空；
• salary，薪水，数字类型，必须大于零（emp_salary_min）；
• commission_pct，佣金百分比，数字类型；
• manager_id，经理编号，外键（通过外键emp_manager_fk引用员工表的员工编号）；
• department_id，部门编号，外键（通过外键emp_dept_fk引用部门表departments的编号department_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;

模式搜索路径

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

CREATE TABLE schema_name.table_name...

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

test=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

搜索路径是一个逗号分隔的模式名称。当使用表的时候，PostgreSQL会依次在这些模式中进行查找，返回第一个匹配的表名；当创建一个新表时，如果没有指定模式名称，PostgreSQL会在第一个模式中进行创建。第一个模式默认为当前用户名，如果不存在该模式，使用后面的公共模式（public）

test=# select user;
   user   
----------
 postgres
(1 row)

test=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 hr     | wdh
 public | postgres

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

test=# \d
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | departments | table | postgres
 public | employees   | table | postgres

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

test=# SET search_path TO hr,public;

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