PostgreSQL 创建数据表

创建数据表是数据库管理中最基础也是最核心的操作。它涉及定义数据的结构、指定用于存储信息的列（字段），并为每一列决定合适的数据类型。

一个设计良好的表结构对于高效地存储、检索和操作数据至关重要。同时，它也能确保整个数据库的数据完整性和一致性。本章将手把手教你在 PostgreSQL 中创建数据表，重点讲解如何定义列以及选择正确的数据类型。

1. 理解 PostgreSQL 中的数据表

在 PostgreSQL 中，表（Table）是以行（Row）和列（Column）形式组织的相关数据的集合。每一行代表一条单独的记录，每一列代表该记录的一个特定属性。

你可以把数据表想象成一个电子表格（如 Excel）：每一行是一条完整的数据，每一列是关于这条数据的一项具体信息。在真正动手建表之前，仔细规划表结构非常重要。这包括：确定你要存储什么数据、用哪些列来表示这些数据，以及为每列选择什么数据类型。

例如，如果你正在为一个图书馆构建数据库，你可能会有一个名为 books（书籍）的表。这个表将包含图书馆中每本书的信息，如书名 (title)、作者 (author)、国际标准书号 (ISBN)、出版日期 (publication date) 和体裁 (genre)。这里的每一项信息，都会成为表中的一列。

2. CREATE TABLE 语句

CREATE TABLE 语句用于在 PostgreSQL 中创建一个新表。它的基础语法如下：

CREATE TABLE table_name (
    column1_name data_type1,
    column2_name data_type2,
    column3_name data_type3,
    ...
);

• CREATE TABLE: 这是一个关键字指令，告诉数据库你要创建一个新表。
• table_name: 这是你想给表起的名字。表名应该具有描述性，并遵循命名规范（例如：使用小写字母和下划线）。
• column1_name, column2_name...: 这些是表中的列名。列名同样应该具有描述性并遵循命名规范。
• data_type1, data_type2...: 这些是对应列的数据类型。数据类型规定了该列可以存储哪种类型的数据（如：整数、文本、日期）。

让我们来看一个实际的例子，创建一个名为 employees（员工）的表，包含员工ID、名字、姓氏和入职日期：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

• employee_id: 存储每个员工的唯一标识符。SERIAL 是 PostgreSQL 特有的数据类型，它会自动生成一连串的自增整数，非常适合用作主键。PRIMARY KEY (主键) 约束确保该列中的每个值都是唯一的，且不能为空。
• first_name: 存储员工的名字。VARCHAR(50) 是一种存储可变长度字符串的数据类型，这里的最大长度限制为 50 个字符。
• last_name: 存储员工的姓氏。同样使用了 VARCHAR(50)。
• hire_date: 存储员工入职的日期。DATE 是专门用于存储日期的数据类型。

3. 定义列 (Columns)

表中的每一列都必须有一个名称和一个数据类型。

在定义列时，请牢记以下几个重要方面：

• 列名 (Column Names): 选择能清晰表达该列用途的描述性名称。习惯上使用小写字母和下划线分隔（例如 customer_id, product_name）。尽量避免使用 SQL 保留字作为列名。
• 数据类型 (Data Types): 根据要存储的数据种类选择合适的数据类型。正确的数据类型既能保证数据完整性，又能优化存储空间。
• 约束 (Constraints): 你可以为列添加约束，以强制执行数据完整性规则。常见的约束包括：
• NOT NULL: 确保该列不能包含空值。
• UNIQUE: 确保该列中的所有值都是唯一的。
• PRIMARY KEY: 唯一标识表中的每一行。
• FOREIGN KEY: 建立两个表之间的关联关系。

4. PostgreSQL 常用数据类型

PostgreSQL 提供了极其丰富的数据类型来容纳各种数据。以下是最常用的一些：

4.1 整数类型 (Integer Types)

• SMALLINT: 存储小范围整数（通常是 -32,768 到 32,767）。
• INTEGER 或 INT: 存储标准整数（通常是 -2,147,483,648 到 2,147,483,647）。
• BIGINT: 存储极大范围的整数。
• SERIAL: 自动生成自增整数序列，常用于主键。BIGSERIAL 则是针对 BIGINT 的自增版本。

示例：

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    quantity_in_stock INTEGER
);

(这里 product_id 自动生成唯一ID，quantity_in_stock 用整数存储库存数量。)

4.2 字符类型 (Character Types)

• VARCHAR(n): 存储最大长度为 n 的可变长度字符串。
• TEXT: 存储可变长度字符串，实际上没有长度限制（极度灵活）。
• CHAR(n): 存储固定长度为 n 的字符串。如果存入的字符串较短，系统会自动用空格补齐。

示例：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email TEXT
);

(名字使用 VARCHAR(50) 限制长度，而 email 地址长度差异较大，使用了 TEXT。)

4.3 数值/浮点类型 (Numeric Types)

• DECIMAL(p, s) 或 NUMERIC(p, s): 存储精确的数值。p 代表总位数，s 代表小数点后的位数。对精度要求极高的财务数据非常有用。
• REAL: 存储单精度浮点数。
• DOUBLE PRECISION: 存储双精度浮点数。

示例：

CREATE TABLE items (
    item_id SERIAL PRIMARY KEY,
    price DECIMAL(10, 2),
    weight DOUBLE PRECISION
);

(价格 price 最高允许10位数字，其中小数点后占2位。重量 weight 则使用双精度浮点数。)

4.4 日期和时间类型 (Date and Time Types)

• DATE: 仅存储日期（年、月、日）。
• TIME: 仅存储时间（时、分、秒）。
• TIMESTAMP: 同时存储日期和时间。
• TIMESTAMP WITH TIME ZONE: 存储带有时区信息的日期和时间。

示例：

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP WITH TIME ZONE
);

4.5 布尔类型 (Boolean Type)

• BOOLEAN: 存储逻辑布尔值（true 真 或 false 假）。

示例：

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    is_completed BOOLEAN
);

(is_completed 标记任务是否已完成。)

其他数据类型：PostgreSQL 还支持诸如 JSON, JSONB, UUID, 数组 (arrays) 和几何类型等高级数据结构，这在处理复杂数据时非常有用。

5. 创建数据表的综合示例

让我们看几个结合了不同数据类型和约束条件的进阶建表案例。

示例 1：创建 customers (客户) 表

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,         -- 自动生成的唯一主键
    first_name VARCHAR(50) NOT NULL,        -- 名字，不能为空
    last_name VARCHAR(50) NOT NULL,         -- 姓氏，不能为空
    email VARCHAR(100) UNIQUE,              -- 邮箱，必须唯一
    phone_number VARCHAR(20),               -- 电话号码
    address TEXT,                           -- 详细地址
    registration_date DATE                  -- 注册日期
);

示例 2：创建 orders (订单) 表

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,                        
    customer_id INTEGER REFERENCES customers(customer_id), -- 外键：关联客户表
    order_date TIMESTAMP WITH TIME ZONE,                   -- 下单时间（含时区）
    total_amount DECIMAL(10, 2),                           -- 订单总金额
    shipping_address TEXT                                  -- 送货地址
);

(注意 REFERENCES customers(customer_id) 建立了一个外键关联，指明这个订单属于哪个客户。)

示例 3：创建 products (产品) 表

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50),
    is_available BOOLEAN DEFAULT TRUE       -- 是否上架，默认值为 true
);

(DEFAULT TRUE 表示如果在插入数据时没有指定该字段的值，它将自动默认为真。)