PostgreSQL - CREATE

PostgreSQL - 创建表

CREATE TABLE 语句是 PostgreSQL 中定义数据结构的基本命令。它在当前数据库中创建一个新的、初始为空的表。这涉及为表命名、定义其列、为每列指定数据类型以及应用约束。

语法

CREATE TABLE [ IF NOT EXISTS ] table_name (
    column_name1 data_type [ column_constraint [ ... ] ],
    column_name2 data_type [ column_constraint [ ... ] ],
    ...
    [ table_constraint [ ... ] ]
);

关键组成部分：

table_name：在模式内为您的表指定的唯一名称。
column_name：列的名称。
data_type：列将存储的数据类型（例如，INTEGER、TEXT、TIMESTAMP）。
column_constraint：应用于单个列的规则（例如，NOT NULL、UNIQUE）。
table_constraint：应用于一个或多个列的规则（例如，PRIMARY KEY、FOREIGN KEY）。

现代数据类型与最佳实践

选择正确的数据类型对于数据完整性、性能和存储效率至关重要。以下是一些常用的现代数据类型：

标识列（Identity Columns）：BIGINT GENERATED ALWAYS AS IDENTITY 是创建自增主键的现代 SQL 标准方式。优先使用此方式，而非旧的 SERIAL 类型。
文本类型（Text）：对于可变、无限长度的字符串，请使用 TEXT。仅当您需要强制执行特定最大长度时才使用 VARCHAR(n)。
数值类型（Numeric）：对于金融计算，请始终使用 NUMERIC(precision, scale) 以避免浮点舍入误差。对于科学计算，请使用 REAL 或 DOUBLE PRECISION。
日期和时间类型（Date and Time）：TIMESTAMP WITH TIME ZONE（别名 TIMESTAMPTZ）是存储时间戳的最佳实践。它以 UTC 存储时间，并在检索时将其转换为客户端的时区，从而消除歧义。
唯一标识符（Unique Identifiers）：UUID 非常适用于在分布式系统中生成唯一键。
JSON 类型：JSONB 是存储 JSON 数据的首选类型。它以分解的二进制格式存储，查询速度更快且支持索引。

示例：创建相关表

让我们创建两个相关表 users 和 orders，以模拟一个简单的电子商务系统。这展示了主键、外键和其他约束。

-- 首先，创建 'users' 表
CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 接下来，创建 'orders' 表，其中包含引用 'users' 的外键
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0),
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),

    -- 定义外键关系
    CONSTRAINT fk_user
        FOREIGN KEY(user_id)
        REFERENCES users(user_id)
        ON DELETE SET NULL -- 如果用户被删除，将 orders 表中的 user_id 设置为 NULL
);

验证表创建

在 psql 中，您可以使用 \d 命令检查您已创建的表的结构。

my_app_db=# \d users

此命令将显示 users 表的列、它们的类型、修饰符以及与之关联的任何索引或约束。

                                     Table "public.users"
   Column   |           Type           | Collation | Nullable |               Default
------------+--------------------------+-----------+----------+--------------------------------------
 user_id    | bigint                   |           | not null | generated always as identity
 username   | text                     |           | not null |
 email      | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()
 updated_at | timestamp with time zone |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
Check constraints:
    "users_email_check" CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'::text)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL

高级概念

对于更复杂的场景，PostgreSQL 支持高级功能，例如：

临时表（Temporary Tables）：CREATE TEMP TABLE ... 创建一个在当前会话结束时自动删除的表。这对于中间计算很有用。
表继承（Table Inheritance）：一个表可以从一个或多个父表继承列。
分区（Partitioning）：对于超大型表，您可以根据键或范围将它们分区为更小、更易于管理的片段，这可以显著提高查询性能。