テーブル

テーブルとはデータを格納する場所です。

テーブルはExcelのスプレッドシートに似ています。データの列と行を含んでいます。 例えば、このテーブルには3つの「列」（id, name, description）と4つの「行」のデータがあります。

id	name	description
1	The Phantom Menace	Two Jedi escape a hostile blockade to find allies and come across a young boy who may bring balance to the Force.
2	Attack of the Clones	Ten years after the invasion of Naboo, the Galactic Republic is facing a Separatist movement.
3	Revenge of the Sith	As Obi-Wan pursues a new threat, Anakin acts as a double agent between the Jedi Council and Palpatine and is lured into a sinister plan to rule the galaxy.
4	Star Wars	Luke Skywalker 結合s forces with a Jedi Knight, a cocky pilot, a Wookiee and two droids to save the galaxy from the Empire's world-destroying battle station.

表計算ソフトとの重要な違いがいくつかありますが、リレーショナル・データベースに初めて触れる方には良い出発点になります。

テーブルの作成

テーブルを作成する際には、同時にカラムを追加するのがベストプラクティスです。

各カラムの「データ型」は、作成時に定義する必要があります。カラムの追加や削除は、テーブルを作成した後いつでも行うことができます。

Supabaseには、テーブルを作成するためのいくつかの選択肢があります。テーブル・エディターを使用するか、SQLを直接使用して作成できます。 ダッシュボードにはSQLエディターが用意されていますが、データベースに接続して、SQLクエリを実行できます。

Dashboard

1. 「Table editor」のセクションに移動します。
2. 「New Table」をクリックします。
3. 「todos」というテーブル名を入力します。
4. 「Save」をクリックします。
5. 「New Column」をクリックします。
6. 「task」というカラム名を入力して、タイプを「text」にします。
7. 「Save」をクリックします。

SQL

create table movies (
  id bigint generated by default as identity primary key,
  name text,
  description text
);

テーブル名を付ける際には、小文字とアンダースコアを使用するのがベストプラクティスです。例えば、Table Nameではなく、table_nameとします。

カラム

カラムを作成する際に、「データ型」を定義します。

データ型

全ての列は定義済みの型です。PostgreSQLは多くのデフォルト型を提供していますが、デフォルト型がニーズに合わない場合は、独自に設計できます（または拡張機能を使用きます）。

表示/非表示 デフォルトのデータ型

名称	エイリアス	説明
bigint	int8	符号付き8バイト整数
bigserial	serial8	オートインクリメントの8バイト整数
bit		固定長のビット列
bit varying	varbit	可変長のビット列
boolean	bool	論理ブーリアン（true/false）
box		平面上の長方形の短形
bytea		バイナリーデータ（バイトの配列）
character	char	固定長の文字列
character varying	varchar	可変長文字列
cidr		IPv4またはIPv6のネットワークアドレス
circle		平面上の円
date		カレンダーの日付（年、月、日）
double precision	float8	倍精度の浮動小数点数（8バイト）
inet		IPv4またはIPv6のホストアドレス
integer	int, int4	符号付き4バイト整数
interval [ fields ]		タイムスパン
json		テキスト形式のJSONデータ
jsonb		JSONのバイナリーデータを分解したもの
line		平面上の無限の線
lseg		平面上の線分
macaddr		MAC（Media Access Control）アドレス
macaddr8		MAC（Media Access Control）アドレス（EUI-64形式）
money		通貨量
numeric	decimal	選択可能な精度の正確な数値
path		平面上の幾何学的なパス
pg_lsn		PostgreSQLログのシーケンス番号
pg_snapshot		ユーザーレベルのトランザクションIDのスナップショット
point		平面上の幾何学的な点
polygon		平面上の閉じた幾何学的経路
real	float4	単精度の浮動小数点数（4バイト）
smallint	int2	符号付き2バイト整数
smallserial	serial2	オートインクリメントの2バイト整数
serial	serial4	オートインクリメントの4バイト整数
text		可変長文字列
time [ without time zone ]		時間帯（タイムゾーンなし）
time with time zone	timetz	タイムゾーンを含む時刻
timestamp [ without time zone ]		日付と時刻（タイムゾーンなし）
timestamp with time zone	timestamptz	タイムゾーンを含む日付と時刻
tsquery		テキスト検索クエリ
tsvector		テキスト検索ドキュメント
txid_snapshot		ユーザレベルのトランザクションIDのスナップショット（非推奨、pg_snapshot参照）
uuid		一意に識別するための識別子 - universally unique identifier
xml		XMLデータ

ある型のカラムを別の型に「キャスト」できますが、型の間にいくつかの非互換性が存在します。 例えば、timestampをdateにキャストすると、それまで保存されていた時刻の情報がすべて失われます。

プライマリー・キー

テーブルには「プライマリー・キー」、つまりすべてのデータ行に一意な識別子を設定できます。プライマリー・キーに関するいくつかのヒントを紹介します。

• データベース内のすべてのテーブルにプライマリー・キーを作成することを推奨します。
• 各行で一意であれば、どのようなカラムでもプライマリー・キーとして使用できます。
• プライマリー・キーとしてuuid型または番号付きのidentityカラムを使用するのが一般的です。

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を使用することで、独自の値を挿入できます。

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

データの読み込み

Supabaseにデータを読み込むには、いくつかの方法があります。データベースに直接データを読み込む方法と、APIを使用して読み込みする方法があります。 大きなデータ・セットを読み込む場合は、「バルクでの読み込み」の手順を使用してください。

基本的なデータの読み込み

SQL

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.');

JavaScript

const { data, error } = await supabase
  .from('movies')
  .insert([{
    name: 'The Empire Strikes Back',
    description: 'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.'
  }, {
    name: 'Return of the Jedi',
    description: 'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.'
  }])

Dart

final res = await supabase
  .from('movies')
  .insert([{
    name: 'The Empire Strikes Back',
    description: 'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.'
  }, {
    name: 'Return of the Jedi',
    description: 'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.'
  }]).execute();

バルクでのデータ読み込み

大きなデータのセットを挿入する場合、PostgreSQLのCOPYコマンドを使用することが最善です。 これは、データをファイルからテーブルに直接ロードします。データのコピーに使用できるファイル形式は、テキスト、csv、バイナリー、JSONなど、いくつかあります。

例えば、次のCSVファイルをmoviesテーブルに読み込みます。

./movies.csv

"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コマンドでファイルを読み込みます。

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

外部キーでテーブル結合

テーブルは、外部キーを使って「結合（結合）」できます。

これは、データが通常ある種の関係を形成していることから、「リレーショナル（関連）」という名称が付けられました。

上記の「movies（映画）」の例では、各映画に「カテゴリー」を追加したいとします（例えば、「アクション」や「ドキュメンタリー」など）。 そこで、categoriesという新しいテーブルを作成して、moviesテーブルに「リンク」してみましょう。

create table categories (
  id bigint generated always as identity primary key,
  name text -- category name
);

alter table movies
  add column category_id bigint references categories;

また、「結合」テーブルを作成することで、「多対多」の関係を作ることができます。 例えば、次のような状況があったとします。

• movies（映画）のリストがあります。
• 映画には複数のactors（俳優）が登場します。
• actorは複数の映画に出演できます。

SQL

create table movies (
  id bigint generated by default as identity primary key,
  name text,
  description text
);

create table actors (
  id bigint generated by default as identity primary key,
  name text
);

create table performances (
  id bigint generated by default as identity primary key,
  movie_id bigint not null references movies,
  actor_id bigint not null references actors
);

Dashboard

スキーマ

テーブルはschemasに属します。スキーマはテーブルを組織化する方法であり、多くの場合、セキュリティー上の理由からです。

テーブルを作成する際にスキーマを明示的に渡さない場合、Postgresはpublicスキーマでテーブルを作成するものとみなします。

テーブルを整理するためのスキーマを作成できます。例えば、APIからは見えないプライベートなスキーマが必要な場合は次のように作成します。

create schema private;

これでprivateスキーマの中にテーブルを作成できるようになりました。

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

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.

caution

By default, PostgreSQL views bypass Row Level Security unless you change their owner (see https://github.com/supabase/supabase/discussions/901). PostgreSQL v15 (coming soon) will have a more intuitive control for this through security invoker views and the previous step won't be needed.

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

students

id	name	type
1	Princess Leia	undergraduate
2	Yoda	graduate
3	Anakin Skywalker	graduate

courses

id	title	code
1	Introduction to Postgres	PG101
2	Authentication Theories	AUTH205
3	Fundamentals of Supabase	SUP412

grades

id	student_id	course_id	result
1	1	1	B+
2	1	3	A+
3	2	2	A
4	3	1	A-
5	3	2	A
6	3	3	B-

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

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;
    
alter view transcripts owner to authenticated;

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

select * from transcripts;

When to use views

Views provide the several benefits:

• Simplicity
• Consistency
• Logical Organization
• Security

Simplicity

As a query becomes complex it becomes a hassle to call it. Especially when we run it at regularly. In the example above, instead of repeatedly running:

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;

We can run this instead:

select * from transcripts;

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

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:

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
where courses.code != 'PG101';

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 just the underlying query in the view transcripts. The change will be applied to all applications using this view.

Logical Organization

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 easily 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:

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;

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

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:

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.

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.

リソース

• 公式ドキュメント：テーブル作成
• Official Docs: Create view
• PostgreSQLチュートリアル：テーブル作成
• PostgreSQLチュートリアル：カラム追加
• PostgreSQL Tutorial: Views