表格和数据

表格就是用来存储数据的地方。

🌐 Tables are where you store your data.

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

🌐 Tables are similar to excel spreadsheets. They contain columns and rows. For example, this table has 3 "columns" (id, name, description) and 4 "rows" of data:

和电子表格相比有一些重要的不同，但如果你是关系型数据库的新手，这是一个不错的起点。

🌐 There are a few important differences from a spreadsheet, but it's a good starting point if you're new to Relational databases.

创建表格 #

🌐 Creating tables

创建表格时，最好一次性添加所有列。

🌐 When creating a table, it's best practice to add columns at the same time.

在创建每一列时，你必须定义它的“数据类型”。创建表之后，你可以随时添加或删除列。

🌐 You must define the "data type" of each column when it is created. You can add and remove columns at any time after creating a table.

Supabase 提供了多种创建表的选项。你可以使用仪表板，或直接用 SQL 创建它们。我们在仪表板中提供了一个 SQL 编辑器，或者你也可以连接到你的数据库，自己运行 SQL 查询。

🌐 Supabase provides several options for creating tables. You can use the Dashboard or create them directly using SQL. We provide a SQL editor within the Dashboard, or you can connect to your database and run the SQL queries yourself.

列 #

🌐 Columns

当你创建一个列时，你必须定义“数据类型”。

🌐 You must define the "data type" when you create a column.

数据类型 #

🌐 Data types

每一列都有一个预定义的类型。Postgres 提供了很多默认类型，如果默认类型不符合你的需求，你甚至可以设计自己的类型（或者使用扩展）。你可以在 SQL 编辑器中使用 Postgres 支持的任何数据类型。为了让使用数据库经验较少的人也能轻松上手，我们在表格编辑器中只支持其中的一部分类型。

🌐 Every column is a predefined type. Postgres provides many default types, and you can even design your own (or use extensions) if the default types don't fit your needs. You can use any data type that Postgres supports via the SQL editor. We only support a subset of these in the Table Editor in an effort to keep the experience focused for people with less experience with databases.

你可以将列从一种类型“转换”到另一种类型，但类型之间可能存在一些不兼容的情况。例如，如果你把 timestamp 转换成 date，之前保存的所有时间信息都会丢失。

🌐 You can "cast" columns from one type to another, however there can be some incompatibilities between types. For example, if you cast a timestamp to a date, you will lose all the time information that was previously saved.

主键 #

🌐 Primary keys

一张表可以有一个“主键”——每一行数据的唯一标识。关于主键，有几点小提示：

🌐 A table can have a "primary key" - a unique identifier for every row of data. A few tips for Primary Keys:

• 建议给数据库里的每个表创建一个主键。
• 你可以使用任何列作为主键，只要它对每一行都是唯一的。
• 通常会使用 uuid 类型或编号的 identity 列作为主键。

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

在上面的例子中，我们有：

🌐 In the example above, we have:

1. 创建了一个叫做 id 的列
2. 分配了数据类型 bigint
3. 告诉数据库这个应该是 generated always as identity，这意味着 Postgres 会自动给这一列分配一个唯一的数字。
4. 因为它很独特，我们也可以把它当作我们的 primary key。

我们也可以使用 generated by default as identity，这样就可以插入我们自己独特的值。

🌐 We could also use generated by default as identity, which would allow us to insert our own unique values.

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

正在加载数据 #

🌐 Loading data

在 Supabase 中有几种加载数据的方式。你可以直接将数据加载到数据库中，或者使用 API。如果你要加载大量数据，请使用“批量加载”说明。

🌐 There are several ways to load data in Supabase. You can load data directly into the database or using the APIs. Use the "Bulk Loading" instructions if you are loading large data sets.

基础数据加载 #

🌐 Basic data loading

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

批量数据加载 #

🌐 Bulk data loading

在插入大数据集时，最好使用 Postgres 的 COPY 命令。这会将数据直接从文件加载到表中。用于复制数据的文件格式有多种：文本、CSV、二进制、JSON 等。

🌐 When inserting large data sets it's best to use Postgres's COPY command. This loads data directly from a file into a table. There are several file formats available for copying data: text, CSV, binary, JSON, etc.

例如，如果你想把一个 CSV 文件加载到你的 movies 表里：

🌐 For example, if you wanted to load a CSV file into your movies table:

1
"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."
2
"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 命令加载文件：

🌐 You would connect to your database directly and load the file with the COPY command:

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

另外，使用 Postgres COPY 文档中定义的 DELIMITER、HEADER 和 FORMAT 选项。

🌐 Additionally use the DELIMITER, HEADER and FORMAT options as defined in the Postgres COPY docs.

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

如果你收到错误 FATAL: password authentication failed for user "postgres"，就在数据库设置里重置你的数据库密码，然后再试一次。

🌐 If you receive an error FATAL: password authentication failed for user "postgres", reset your database password in the Database Settings and try again.

使用外键连接表 #

🌐 Joining tables with foreign keys

表可以使用外键“连接”在一起。

🌐 Tables can be "joined" together using Foreign Keys.

这就是“关系型”命名的由来，因为数据通常会形成某种关系。

🌐 This is where the "Relational" naming comes from, as data typically forms some sort of relationship.

在我们上面的“电影”例子中，我们可能想要为每部电影添加一个“类别”（例如，“动作片”或“纪录片”）。 创建一个名为 categories 的新表，并将其链接到 movies 表。

🌐 In our "movies" example above, we might want to add a "category" for each movie (for example, "Action", or "Documentary"). Create a new table called categories and link it to the movies table.

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

你也可以通过创建“关联”表来建立“多对多”的关系。例如，如果你遇到以下情况：

🌐 You can also create "many-to-many" relationships by creating a "join" table. For example if you had the following situations:

• 你有一个 movies 的清单。
• 一部电影可以有好几个 actors。
• actor 可以在几部电影中表演。

模式 #

🌐 Schemas

表属于 schemas。模式是组织表的一种方式，通常出于安全原因。

🌐 Tables belong to schemas. Schemas are a way of organizing your tables, often for security reasons.

如果你在创建表时没有显式指定模式，Postgres 会默认你想在 public 模式下创建表。

🌐 If you don't explicitly pass a schema when creating a table, Postgres will assume that you want to create the table in the public schema.

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

🌐 We can create schemas for organizing tables. For example, we might want a private schema which is hidden from our API:

1
create schema private;

现在我们可以在 private 模式里创建表了：

🌐 Now we can create tables inside the private schema:

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

浏览量 #

🌐 Views

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

🌐 A View is a convenient shortcut to a query. Creating a view does not involve new tables or data. When run, an underlying query is executed, returning its results to the user.

假设我们有一个大学数据库中的以下表格：

🌐 Say we have the following tables from a database of a university:

students

courses

grades

创建一个包含所有三张表的视图看起来像这样：

🌐 Creating a view consisting of all the three tables will look like this:

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

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

🌐 Once done, we can now access the underlying query with:

1
select * from transcripts;

查看安全 #

🌐 View security

默认情况下，视图的访问权限是由创建者来控制的（“安全定义者”）。如果一个有特权的角色创建了视图，其他人访问时会使用该角色的提升权限。要强制执行行级安全策略，可以使用“安全调用者”修饰符来定义视图。

🌐 By default, views are accessed with their creator's permission ("security definer"). If a privileged role creates a view, others accessing it will use that role's elevated permissions. To enforce row level security policies, define the view with the "security invoker" modifier.

1
-- alter a security_definer view to be security_invoker
2
alter view <view name>
3
set (security_invoker = true);
4
5
-- create a view with the security_invoker modifier
6
create view <view name> with(security_invoker=true) as (
7
  select * from <some table>
8
);

什么时候使用视图 #

🌐 When to use views

视图提供几个好处：

🌐 Views provide several benefits:

• 简单
• 一致性
• 逻辑组织
• 安全

简单 #

🌐 Simplicity

当一个查询变得更复杂时，一遍又一遍地调用它会很麻烦——尤其是当我们经常运行它的时候。在上面的例子中，不用反复运行：

🌐 As a query becomes more complex, it can be a hassle to call it over and over - especially when we run it regularly. In the example above, instead of repeatedly running:

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

我们可以改运行这个：

🌐 We can run this instead:

1
select * from transcripts;

此外，视图的行为就像普通表一样。我们可以放心地在表 JOIN 中使用它，甚至可以用现有的视图创建新的视图。

🌐 Additionally, a view behaves like a typical table. We can safely use it in table JOINs or even create new views using existing views.

一致性 #

🌐 Consistency

视图可以确保在重复执行查询时错误的可能性降低。在上面的例子中，我们可能决定想要排除课程 Introduction to Postgres。查询将变成这样：

🌐 Views ensure that the likelihood of mistakes decreases when repeatedly executing a query. In our example above, we may decide that we want to exclude the course Introduction to Postgres. The query would become:

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

如果没有视图，我们就需要进入每个依赖的查询去添加新的规则。这会增加出错和不一致的可能性，也会给开发者带来很多额外工作。有了视图，我们可以修改视图 transcripts 中的底层查询。这个更改会应用到所有使用该视图的应用上。

🌐 Without a view, we would need to go into every dependent query to add the new rule. This would increase in the likelihood of errors and inconsistencies, as well as introducing a lot of effort for a developer. With views, we can alter the underlying query in the view transcripts. The change will be applied to all applications using this view.

逻辑组织 #

🌐 Logical organization

通过视图，我们可以给查询起个名字。这对使用同一个数据库的团队非常有用。与其猜测一个查询应该做什么，一个命名合理的视图可以直接说明它的作用。例如，通过查看视图的名字 transcripts（成绩单），我们可以推测底层查询可能涉及 students（学生）、courses（课程） 和 grades（成绩） 表。

🌐 With views, we can give our query a name. This is extremely useful for teams working with the same database. Instead of guessing what a query is supposed to do, a well-named view can explain it. For example, by looking at the name of the view transcripts, we can infer that the underlying query might involve the students, courses, and grades tables.

安全 #

🌐 Security

视图可以限制向用户展示的数据量和类型。与其让用户直接访问一组表，我们会给他们提供一个视图。通过在底层查询中排除敏感列，我们可以阻止他们读取这些列。

🌐 Views can restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. We can prevent them from reading sensitive columns by excluding them from the underlying query.

物化视图 #

🌐 Materialized views

一个物化视图是一种视图的形式，但它也会把结果存储到磁盘上。在后续读取物化视图时，返回结果所需的时间会比普通视图快得多。这是因为物化视图的数据已经准备好了，而普通视图每次被调用时都要执行底层查询。

🌐 A materialized view is a form of view but it also stores the results to disk. In subsequent reads of a materialized view, the time taken to return its results would be much faster than a conventional view. This is because the data is readily available for a materialized view while the conventional view executes the underlying query each time it is called.

用上面我们的例子，可以这样创建一个物化视图：

🌐 Using our example above, a materialized view can be created like this:

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

从物化视图读取数据和从普通视图读取数据是一样的：

🌐 Reading from the materialized view is the same as a conventional view:

1
select * from transcripts;

刷新物化视图 #

🌐 Refreshing materialized views

不幸的是，这有一个权衡——物化视图中的数据并不总是最新的。我们需要定期刷新它，以防数据变得过时。具体做法是：

🌐 Unfortunately, there is a trade-off - data in materialized views are not always up to date. We need to refresh it regularly to prevent the data from becoming too stale. To do so:

1
refresh materialized view transcripts;

你可以自己决定多久刷新一次物化视图，每个视图可能根据它的使用情况有所不同。

🌐 It's up to you how regularly refresh your materialized views, and it's probably different for each view depending on its use-case.

物化视图 vs 常规视图 #

🌐 Materialized views vs conventional views

物化视图在查询或视图的执行时间过慢时很有用。这种情况可能会出现在涉及多张表和数十亿行数据的视图或查询中。然而，在使用这样的视图时，需要对数据可能过时有所容忍。物化视图的一些使用场景包括内部仪表盘和数据分析。

🌐 Materialized views are useful when execution times for queries or views are too slow. These could likely occur in views or queries involving multiple tables and billions of rows. When using such a view, however, there should be tolerance towards data being outdated. Some use-cases for materialized views are internal dashboards and analytics.

创建物化视图并不能解决查询效率低的问题。即使你正在实现物化视图，也应该始终尝试优化运行缓慢的查询。

🌐 Creating a materialized view is not a solution to inefficient queries. You should always seek to optimize a slow running query even if you are implementing a materialized view.

资源 #

🌐 Resources

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