PostgreSQL / 系统课时
创建第一张数据表
创建第一张数据表
本课从零创建一张可实际写入的商品表。你将把列、数据类型、默认值和基础约束组合到 CREATE TABLE 中,并通过插入与查询验证表结构是否符合预期。
学习目标
- 使用
CREATE TABLE定义完整表结构 - 为主键、金额、状态和时间选择合适类型
- 用插入成功与约束失败验证设计
从业务字段到列定义
假设商品需要唯一编号、商品编码、名称、价格、状态和创建时间。表结构不仅描述数据长什么样,还要阻止明显无效的数据进入数据库。
主键使用 bigint 身份列,适合持续增长的内部编号;价格使用定点数 numeric(12,2),避免浮点误差;创建时间使用 timestamptz,保存可正确转换时区的时间点。
创建表
以下 SQL 可在测试数据库中直接执行。显式写出 public.products,可以避免表被当前 search_path 创建到意外的 schema。
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 中通常没有本质性能差距,长度限制的价值主要是表达并校验业务边界。
插入并读取数据
插入一条合法记录,再返回数据库生成的主键和默认值。
INSERT INTO public.products (
product_code,
product_name,
price
)
VALUES (
'P1001',
'机械键盘',
399.00
)
RETURNING product_id, product_code, status, created_at;预期结果:
product_id | product_code | status | created_at
------------+--------------+--------+------------------------
<ID> | P1001 | ACTIVE | <TIME>
(1 row)<ID> 与 <TIME> 是动态值。状态和创建时间未在插入语句中提供,因此由默认值补齐。查询完整记录:
SELECT product_id, product_code, product_name, price, status
FROM public.products
WHERE product_code = 'P1001';预期结果:
product_id | product_code | product_name | price | status
------------+--------------+--------------+--------+--------
<ID> | P1001 | 机械键盘 | 399.00 | ACTIVE
(1 row)验证约束边界
尝试写入负价格,数据库应拒绝该记录。
INSERT INTO public.products (
product_code,
product_name,
price
)
VALUES (
'P1002',
'无效商品',
-1.00
);预期结果:
ERROR: new row for relation "products" violates check constraint "<CONSTRAINT_NAME>"约束名称可能由系统自动生成,因此使用 <CONSTRAINT_NAME> 表示实际值。失败语句不会留下脏数据,这比只依赖应用层校验更可靠。
重复使用 P1001 也会触发唯一约束。唯一约束表达的是数据库范围内的业务不重复规则,后续更新和并发写入同样受保护。
设计时的关键取舍
NOT NULL 用于必填列,但它不能替代内容校验。例如空字符串仍然不是 NULL,若名称不允许空白,还需应用校验或更严格的检查约束。
默认值只在插入时未提供该列时生效。显式写入 NULL 不会自动改成默认值,并且会被 NOT NULL 拒绝。
第一版表结构应覆盖确定的核心规则,不必一次加入所有可能字段。可以通过后续迁移使用 ALTER TABLE 演进,但主键、金额精度和核心唯一键应尽早明确。