メインコンテンツまでスキップ

テーブル

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

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

idnamedescription
1The Phantom MenaceTwo Jedi escape a hostile blockade to find allies and come across a young boy who may bring balance to the Force.
2Attack of the ClonesTen years after the invasion of Naboo, the Galactic Republic is facing a Separatist movement.
3Revenge of the SithAs 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.
4Star WarsLuke 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クエリを実行できます。

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

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

カラム

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

データ型

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

表示/非表示 デフォルトのデータ型
名称エイリアス説明
bigintint8符号付き8バイト整数
bigserialserial8オートインクリメントの8バイト整数
bit固定長のビット列
bit varyingvarbit可変長のビット列
booleanbool論理ブーリアン(true/false)
box平面上の長方形の短形
byteaバイナリーデータ(バイトの配列)
characterchar固定長の文字列
character varyingvarchar可変長文字列
cidrIPv4またはIPv6のネットワークアドレス
circle平面上の円
dateカレンダーの日付(年、月、日)
double precisionfloat8倍精度の浮動小数点数(8バイト)
inetIPv4またはIPv6のホストアドレス
integerint, int4符号付き4バイト整数
interval [ fields ]タイムスパン
jsonテキスト形式のJSONデータ
jsonbJSONのバイナリーデータを分解したもの
line平面上の無限の線
lseg平面上の線分
macaddrMAC(Media Access Control)アドレス
macaddr8MAC(Media Access Control)アドレス(EUI-64形式)
money通貨量
numericdecimal選択可能な精度の正確な数値
path平面上の幾何学的なパス
pg_lsnPostgreSQLログのシーケンス番号
pg_snapshotユーザーレベルのトランザクションIDのスナップショット
point平面上の幾何学的な点
polygon平面上の閉じた幾何学的経路
realfloat4単精度の浮動小数点数(4バイト)
smallintint2符号付き2バイト整数
smallserialserial2オートインクリメントの2バイト整数
serialserial4オートインクリメントの4バイト整数
text可変長文字列
time [ without time zone ]時間帯(タイムゾーンなし)
time with time zonetimetzタイムゾーンを含む時刻
timestamp [ without time zone ]日付と時刻(タイムゾーンなし)
timestamp with time zonetimestamptzタイムゾーンを含む日付と時刻
tsqueryテキスト検索クエリ
tsvectorテキスト検索ドキュメント
txid_snapshotユーザレベルのトランザクションIDのスナップショット(非推奨、pg_snapshot参照)
uuid一意に識別するための識別子 - universally unique identifier
xmlXMLデータ

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

プライマリー・キー

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

  • データベース内のすべてのテーブルにプライマリー・キーを作成することを推奨します。
  • 各行で一意であれば、どのようなカラムでもプライマリー・キーとして使用できます。
  • プライマリー・キーとして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を使用して読み込みする方法があります。 大きなデータ・セットを読み込む場合は、「バルクでの読み込み」の手順を使用してください。

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

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コマンドを使用することが最善です。 これは、データをファイルからテーブルに直接ロードします。データのコピーに使用できるファイル形式は、テキスト、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は複数の映画に出演できます。
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
);

スキーマ

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

Schemas and tables

テーブルを作成する際にスキーマを明示的に渡さない場合、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

idnametype
1Princess Leiaundergraduate
2Yodagraduate
3Anakin Skywalkergraduate

courses

idtitlecode
1Introduction to PostgresPG101
2Authentication TheoriesAUTH205
3Fundamentals of SupabaseSUP412

grades

idstudent_idcourse_idresult
111B+
213A+
322A
431A-
532A
633B-

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.

リソース