表结构和约束准备好之后,接下来要把真实数据写进去。SQL 中改变表内数据的三个核心语句是:
1INSERT:新增行 2UPDATE:修改已有行 3DELETE:删除行
写入不是简单地把字典丢给数据库。SQL 必须明确目标表、涉及的列、写入的值以及要修改哪些行。数据库会在执行过程中检查类型、非空、唯一、外键和其他约束。
先创建一个用户:
1INSERT INTO users (email, display_name) 2VALUES ('xiaoming@example.com', '小明');
没有提供 id 和 created_at 时,PostgreSQL 会分别使用 identity 生成器和 DEFAULT now() 生成对应的值。列名和值按位置一一对应,因此实际项目不应省略列名:表结构变化后,明确列名更安全,也更容易阅读。
id
created_at
DEFAULT now()
普通 INSERT 的命令结果会报告影响了多少行,但不会返回这些行的字段。应用通常还需要新建数据的主键和时间,这时可以使用 PostgreSQL 的 RETURNING:
INSERT
RETURNING
1INSERT INTO users (email, display_name) 2VALUES ('xiaohong@example.com', '小红') 3RETURNING id, email, display_name, created_at;
RETURNING 返回数据库最终保存的值,包括自动生成的主键和默认时间。相比插入后再执行一次查询,它既减少了一次数据库往返,也不需要额外设计查询条件来重新定位刚插入的行。
identity 的值来自序列,只保证自动生成,不保证连续。即使某次插入因为约束错误而失败,已经取得的序列值通常也不会回收。因此,业务代码不能猜测下一条数据的 ID,也不能把 ID 是否连续当作业务规则;需要使用新 ID 时,应读取 RETURNING 的结果。
一次插入多行时,每个值组都必须与列列表对应:
01INSERT INTO projects (owner_id, name) 02VALUES 03 ( 04 (SELECT id FROM users WHERE email = 'xiaoming@example.com'), 05 '网站重构' 06 ), 07 ( 08 (SELECT id FROM users WHERE email = 'xiaoming@example.com'), 09 '学习计划' 10 ) 11RETURNING id, owner_id, name;
这里没有假设小明的 ID 一定是 1,而是根据具有唯一约束的邮箱查询真实 ID。标量子查询最多只能返回一个值;如果没有找到小明,子查询结果就是 NULL,owner_id 的 NOT NULL 约束会阻止插入。
1
NULL
owner_id
NOT NULL
单条多行 INSERT 中任意一行失败,整个语句都不会留下部分成功的数据。执行成功后应记下 RETURNING 返回的项目 ID,后续写入任务时会用它建立关联。
此段落已有评论,按 Enter 查看评论及回复列表。
连接 GitHub 后查看和发表评论,也可在 GitHub 查看公开讨论。
1. 数据操作
表结构和约束准备好之后,接下来要把真实数据写进去。SQL 中改变表内数据的三个核心语句是:
写入不是简单地把字典丢给数据库。SQL 必须明确目标表、涉及的列、写入的值以及要修改哪些行。数据库会在执行过程中检查类型、非空、唯一、外键和其他约束。
先创建一个用户:
没有提供
id和created_at时,PostgreSQL 会分别使用 identity 生成器和DEFAULT now()生成对应的值。列名和值按位置一一对应,因此实际项目不应省略列名:表结构变化后,明确列名更安全,也更容易阅读。返回写入结果
普通
INSERT的命令结果会报告影响了多少行,但不会返回这些行的字段。应用通常还需要新建数据的主键和时间,这时可以使用 PostgreSQL 的RETURNING:RETURNING返回数据库最终保存的值,包括自动生成的主键和默认时间。相比插入后再执行一次查询,它既减少了一次数据库往返,也不需要额外设计查询条件来重新定位刚插入的行。identity 的值来自序列,只保证自动生成,不保证连续。即使某次插入因为约束错误而失败,已经取得的序列值通常也不会回收。因此,业务代码不能猜测下一条数据的 ID,也不能把 ID 是否连续当作业务规则;需要使用新 ID 时,应读取
RETURNING的结果。一次插入多行时,每个值组都必须与列列表对应:
这里没有假设小明的 ID 一定是
1,而是根据具有唯一约束的邮箱查询真实 ID。标量子查询最多只能返回一个值;如果没有找到小明,子查询结果就是NULL,owner_id的NOT NULL约束会阻止插入。单条多行
INSERT中任意一行失败,整个语句都不会留下部分成功的数据。执行成功后应记下RETURNING返回的项目 ID,后续写入任务时会用它建立关联。