PostgreSQL / 系统课时

创建第一张数据表

创建第一张数据表

本课从零创建一张可实际写入的商品表。你将把列、数据类型、默认值和基础约束组合到 CREATE TABLE 中,并通过插入与查询验证表结构是否符合预期。

学习目标

  • 使用 CREATE TABLE 定义完整表结构
  • 为主键、金额、状态和时间选择合适类型
  • 用插入成功与约束失败验证设计

从业务字段到列定义

假设商品需要唯一编号、商品编码、名称、价格、状态和创建时间。表结构不仅描述数据长什么样,还要阻止明显无效的数据进入数据库。

主键使用 bigint 身份列,适合持续增长的内部编号;价格使用定点数 numeric(12,2),避免浮点误差;创建时间使用 timestamptz,保存可正确转换时区的时间点。

创建表

以下 SQL 可在测试数据库中直接执行。显式写出 public.products,可以避免表被当前 search_path 创建到意外的 schema。

sql
CREATE TABLE public.products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_code varchar(32) NOT NULL UNIQUE,
    product_name varchar(100) NOT NULL,
    price numeric(12, 2) NOT NULL CHECK (price >= 0),
    status varchar(16) NOT NULL DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE', 'INACTIVE')),
    created_at timestamptz NOT NULL DEFAULT current_timestamp
);

生效行为:PostgreSQL 创建一张空表,并同时建立主键、唯一约束与两个检查约束。身份列由数据库生成编号,普通插入不需要提供 product_id。

字段长度应来自真实业务规则,而不是随意使用很大的上限。varchar(100) 与 text 在 PostgreSQL 中通常没有本质性能差距,长度限制的价值主要是表达并校验业务边界。

插入并读取数据

插入一条合法记录,再返回数据库生成的主键和默认值。

sql
INSERT INTO public.products (
    product_code,
    product_name,
    price
)
VALUES (
    'P1001',
    '机械键盘',
    399.00
)
RETURNING product_id, product_code, status, created_at;

预期结果:

text
 product_id | product_code | status |       created_at
------------+--------------+--------+------------------------
 <ID>       | P1001        | ACTIVE | <TIME>
(1 row)

<ID> 与 <TIME> 是动态值。状态和创建时间未在插入语句中提供,因此由默认值补齐。查询完整记录:

sql
SELECT product_id, product_code, product_name, price, status
FROM public.products
WHERE product_code = 'P1001';

预期结果:

text
 product_id | product_code | product_name | price  | status
------------+--------------+--------------+--------+--------
 <ID>       | P1001        | 机械键盘     | 399.00 | ACTIVE
(1 row)

验证约束边界

尝试写入负价格,数据库应拒绝该记录。

sql
INSERT INTO public.products (
    product_code,
    product_name,
    price
)
VALUES (
    'P1002',
    '无效商品',
    -1.00
);

预期结果:

text
ERROR:  new row for relation "products" violates check constraint "<CONSTRAINT_NAME>"

约束名称可能由系统自动生成,因此使用 <CONSTRAINT_NAME> 表示实际值。失败语句不会留下脏数据,这比只依赖应用层校验更可靠。

重复使用 P1001 也会触发唯一约束。唯一约束表达的是数据库范围内的业务不重复规则,后续更新和并发写入同样受保护。

设计时的关键取舍

NOT NULL 用于必填列,但它不能替代内容校验。例如空字符串仍然不是 NULL,若名称不允许空白,还需应用校验或更严格的检查约束。

默认值只在插入时未提供该列时生效。显式写入 NULL 不会自动改成默认值,并且会被 NOT NULL 拒绝。

第一版表结构应覆盖确定的核心规则,不必一次加入所有可能字段。可以通过后续迁移使用 ALTER TABLE 演进,但主键、金额精度和核心唯一键应尽早明确。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建第一张数据表

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通