在 Postgres 中使用 Create Table

在 PostgreSQL 中，CREATE TABLE 语句用于创建一个新的表。表是数据库的基本构建块，用于存储数据。通过定义表结构，可以组织和管理数据的存储方式。本文将详细介绍在 PostgreSQL 中如何使用 CREATE TABLE 语句，包括其基本语法、各种数据类型、约束条件、表的选项以及常见操作示例。

1. 基本语法

在 PostgreSQL 中，CREATE TABLE 的基本语法如下：

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

• table_name：指定要创建的表的名称。
• column1, column2, …：列的名称和数据类型。
• datatype：列的数据类型，例如 INTEGER, VARCHAR, DATE 等。
• constraints：列的约束条件，例如 NOT NULL, UNIQUE, DEFAULT 等。
• table_constraints：表级别的约束条件，例如主键（PRIMARY KEY）、外键（FOREIGN KEY）等。

2. 数据类型

在 PostgreSQL 中，有多种数据类型可供选择：

• 整型：
  • INTEGER：标准的整数类型。
  • SERIAL：自动递增的整数类型，常用于主键。
• 浮点型：
  • NUMERIC：精确的浮点数类型。
  • FLOAT：标准浮点数类型。
• 字符型：
  • CHAR(n)：定长字符型。
  • VARCHAR(n)：变长字符型。
  • TEXT：可变长度字符型，适用于大文本。
• 日期和时间型：
  • DATE：日期类型。
  • TIMESTAMP：日期和时间类型。
  • TIME：时间类型。
• 布尔型：
  • BOOLEAN：布尔值类型，存储 TRUE 或 FALSE。

3. 约束条件

约束条件用于确保数据的完整性和一致性。常见的约束条件包括：

• PRIMARY KEY：主键约束，唯一标识每一行数据，并且不能为 NULL。
• FOREIGN KEY：外键约束，确保数据的一致性和完整性。
• UNIQUE：唯一约束，确保列中的值唯一。
• NOT NULL：非空约束，确保列中的值不能为空。
• CHECK：检查约束，确保列中的值符合指定条件。
• DEFAULT：默认值约束，为列指定默认值。

4. 表的选项

在创建表时，可以指定一些选项来调整表的行为：

• 继承：通过继承可以创建一个基于其他表的表。例如：

  CREATE TABLE child_table (LIKE parent_table INCLUDING ALL);
  

• 表空间：指定表所在的表空间（存储位置）。例如：

  CREATE TABLE my_table (
      id SERIAL PRIMARY KEY,
      name VARCHAR(100)
  ) TABLESPACE my_tablespace;
  

5. 示例

5.1 创建简单表

创建一个包含员工信息的简单表：

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2) CHECK (salary > 0)
);

在这个示例中：

• emp_id 是自增的主键。
• first_name 和 last_name 是非空的字符串列。
• hire_date 的默认值为当前日期。
• salary 列必须大于零。

5.2 创建表与外键约束

创建一个订单表，并与员工表建立外键关系：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    emp_id INT REFERENCES employees(emp_id) ON DELETE CASCADE,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL
);

在这个示例中：

• order_id 是自增的主键。
• emp_id 是外键，引用 employees 表的 emp_id 列。
• order_date 的默认值为当前时间。
• total_amount 列不能为空。

5.3 创建包含约束和默认值的表

创建一个包含复杂约束的表：

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    price NUMERIC(8, 2) NOT NULL CHECK (price > 0),
    stock INT DEFAULT 0 CHECK (stock >= 0)
);

在这个示例中：

• product_id 是自增的主键。
• product_name 列值唯一且不能为空。
• price 列不能为空且必须大于零。
• stock 列有默认值零，且必须大于或等于零。

6. 常见问题及解决方法

6.1 错误信息“表已经存在”

如果尝试创建一个已存在的表，可能会遇到以下错误：

ERROR: relation "table_name" already exists

解决方法是使用 IF NOT EXISTS 选项来避免错误：

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype [constraints],
    ...
);

6.2 数据类型不匹配

在插入数据时，如果数据类型不匹配，可能会遇到错误。确保插入的数据类型与表的定义匹配。

6.3 约束条件的违反

在插入或更新数据时，如果违反了约束条件，例如 NOT NULL 或 CHECK，可能会遇到错误。确保数据符合所有约束条件。

7. 总结

CREATE TABLE 语句在 PostgreSQL 中用于创建表，是数据库设计的基础。通过理解基本语法、数据类型、约束条件及表的选项，可以高效地设计和管理数据库表。掌握这些知识后，您可以创建复杂的表结构，组织和存储数据，并确保数据的完整性和一致性。通过实践和应用这些知识，可以更好地利用 PostgreSQL 的强大功能，实现高效的数据管理。