数据表与数据

表是您存储数据的地方。

表类似于Excel电子表格，包含列和行。例如，下面这个表有3个"列"（id、name、description）和4"行"数据：

虽然与电子表格有一些重要区别，但如果您是关系型数据库的新手，这是一个很好的起点。

创建数据表

在创建数据表时，最佳实践是同时添加列。

创建列时必须定义其"数据类型"。您可以在创建表后随时添加或删除列。

Supabase 提供了多种创建表的方式。您可以使用仪表盘(Dashboard)或直接使用SQL创建。我们在仪表盘中内置了SQL编辑器，您也可以连接到数据库并自行运行SQL查询。

列定义

创建列时必须指定其"数据类型"。

数据类型

每个列都有预定义的类型。Postgres 提供了许多默认类型，如果默认类型不能满足您的需求，您甚至可以设计自己的类型（或使用扩展）。您可以通过 SQL 编辑器使用 Postgres 支持的任何数据类型。为了简化数据库经验较少的用户的使用体验，我们在表编辑器中仅支持这些类型的子集。

您可以将列从一种类型"转换"（cast）为另一种类型，但类型之间可能存在一些不兼容性。例如，如果将 timestamp 转换为 date，您将丢失之前保存的所有时间信息。

主键

表可以设置"主键"（Primary Key）——用于唯一标识每一行数据的字段。以下是关于主键的几个要点：

• 建议为数据库中的每个表都创建主键
• 任何具有行唯一性的列都可以作为主键
• 通常使用uuid类型或自增的identity列作为主键

1
2
3
create table movies (  id bigint generated always as identity primary key);

在上面的示例中，我们：

1. 创建了一个名为id的列
2. 指定数据类型为bigint
3. 通过generated always as identity指令让Postgres自动为该列分配唯一数值
4. 由于具有唯一性，我们将其设为主键primary key

也可以使用generated by default as identity，这样允许我们插入自定义的唯一值：

1
2
3
create table movies (  id bigint generated by default as identity primary key);

数据加载

Supabase提供多种数据加载方式，您可以直接导入数据库或使用API。如果要加载大型数据集，请参考"批量加载"指南。

基础数据加载

1
2
3
4
5
6
7
8
9
10
11
insert into movies  (name, description)values  (    'The Empire Strikes Back',    'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.'  ),  (    'Return of the Jedi',    'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.'  );

批量数据加载

当需要插入大量数据集时，最佳实践是使用PostgreSQL的COPY命令。该命令直接将文件数据加载到表中。PostgreSQL支持多种数据复制格式：文本、CSV、二进制、JSON等。

例如，如果您想将CSV文件加载到movies表中：

1
2
"The Empire Strikes Back", "After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.""Return of the Jedi", "After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star."

您需要直接连接数据库并使用COPY命令加载文件：

1
2
psql -h DATABASE_URL -p 5432 -d postgres -U postgres \  -c "\COPY movies FROM './movies.csv';"

还可以根据Postgres COPY文档的定义，使用DELIMITER、HEADER和FORMAT等选项：

1
2
psql -h DATABASE_URL -p 5432 -d postgres -U postgres \  -c "\COPY movies FROM './movies.csv' WITH DELIMITER ',' CSV HEADER"

如果遇到错误FATAL: password authentication failed for user "postgres"，请在数据库设置中重置密码后重试。

通过外键关联表

表可以通过外键(Foreign Keys)进行"关联"。

这正是"关系型"数据库名称的由来，因为数据通常会形成某种关联关系。

在我们之前的"movies"示例中，可能想为每部电影添加一个"category"（例如"动作片"或"纪录片"）。 让我们创建一个名为categories的新表，并将它与movies表"关联"。

1
2
3
4
5
6
7
create table categories (  id bigint generated always as identity primary key,  name text -- 分类名称);alter table movies  add column category_id bigint references categories;

您还可以通过创建"关联表"来建立"多对多"关系。 例如以下场景：

• 有一个movies列表
• 一部电影可以有多个actors
• 一个actor可以参演多部电影

模式(Schemas)#

表(table)属于模式(schema)。模式是一种组织表的方式，通常出于安全考虑。

如果在创建表时没有明确指定模式，Postgres会默认在public模式中创建表。

我们可以创建模式来组织表。例如，可能需要一个对API隐藏的私有模式：

1
create schema private;

现在可以在private模式中创建表：

1
2
3
4
5
create table private.salaries (  id bigint generated by default as identity primary key,  salary bigint not null,  actor_id bigint not null references public.actors);

视图(Views)#

视图是查询的便捷快捷方式。创建视图不会涉及新表或数据。当运行视图时，会执行底层查询并将结果返回给用户。

假设我们有一个大学数据库包含以下表：

students学生表

courses课程表

grades（成绩表）

创建一个包含所有三个表的视图如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
create view transcripts as    select        students.name,        students.type,        courses.title,        courses.code,        grades.result    from grades    left join students on grades.student_id = students.id    left join courses on grades.course_id = courses.id;grant all on table transcripts to authenticated;

完成后，我们现在可以通过以下方式访问基础查询：

1
select * from transcripts;

视图安全性

默认情况下，视图使用其创建者的权限（"security definer"）进行访问。如果特权角色创建了视图，其他访问者将使用该角色的提升权限。要强制执行行级安全策略，需使用"security invoker"修饰符定义视图。

1
2
3
4
5
6
7
8
-- 将security_definer视图更改为security_invokeralter view <视图名称>set (security_invoker = true);-- 使用security_invoker修饰符创建视图create view <视图名称> with(security_invoker=true) as (  select * from <某表>);

使用视图的场景

视图提供以下优势：

• 简化操作
• 保持一致性
• 逻辑组织
• 安全性保障

简化性

随着查询变得越来越复杂，反复调用它会变得很麻烦——特别是当我们需要定期运行它时。在上面的例子中，与其重复执行：

1
2
3
4
5
6
7
8
9
10
select  students.name,  students.type,  courses.title,  courses.code,  grades.resultfrom  grades  left join students on grades.student_id = students.id  left join courses on grades.course_id = courses.id;

我们可以直接运行：

1
select * from transcripts;

此外，视图的行为与普通表类似。我们可以安全地在表JOIN中使用它，甚至可以使用现有视图创建新的视图。

一致性

视图能确保在重复执行查询时减少出错的可能性。在上面的例子中，如果我们决定要排除课程《Postgres入门》，查询将变为：

1
2
3
4
5
6
7
8
9
10
11
select  students.name,  students.type,  courses.title,  courses.code,  grades.resultfrom  grades  left join students on grades.student_id = students.id  left join courses on grades.course_id = courses.idwhere courses.code != 'PG101';

如果没有视图，我们需要在每个相关查询中添加新规则。这会增加错误和不一致的可能性，同时给开发者带来大量工作。有了视图，我们只需修改视图transcripts中的基础查询，所有使用该视图的应用都会自动应用这一变更。

逻辑组织

通过视图，我们可以为查询命名。这对于使用同一数据库的团队非常有用。与其猜测查询的功能，一个命名恰当的视图就能说明一切。例如，通过查看视图名称transcripts，我们可以推断出基础查询可能涉及students、courses和grades表。

安全性

视图可以限制向用户展示的数据量和数据类型。我们不是允许用户直接访问一组表，而是为他们提供视图。通过从底层查询中排除敏感列，我们可以防止用户读取这些列。

物化视图

物化视图是视图的一种形式，但它还会将结果存储到磁盘中。在后续读取物化视图时，返回结果所需的时间比常规视图快得多。这是因为物化视图的数据是现成可用的，而常规视图每次被调用时都会执行底层查询。

使用我们上面的示例，可以像这样创建物化视图：

1
2
3
4
5
6
7
8
9
10
11
create materialized view transcripts as  select    students.name,    students.type,    courses.title,    courses.code,    grades.result  from    grades    left join students on grades.student_id = students.id    left join courses on grades.course_id = courses.id;

从物化视图中读取数据的方式与常规视图相同：

1
select * from transcripts;

刷新物化视图

遗憾的是，这里存在一个权衡 - 物化视图中的数据并不总是最新的。我们需要定期刷新它，以防止数据变得过于陈旧。刷新方法如下：

1
refresh materialized view transcripts;

刷新物化视图的频率由您决定，并且根据每个视图的具体用例可能会有所不同。

物化视图 vs 常规视图#

当查询或视图的执行时间过长时，物化视图非常有用。这种情况通常发生在涉及多表和数十亿行数据的视图或查询中。不过使用此类视图时，需要容忍数据可能不是最新的。物化视图的一些使用场景包括内部仪表板和分析。

创建物化视图并不是低效查询的解决方案。即使您正在实现物化视图，也应始终寻求优化运行缓慢的查询。

相关资源

• 官方文档：创建表
• 官方文档：创建视图
• Postgres 教程：创建表
• Postgres 教程：添加列
• Postgres 教程：视图