LabEx
ログイン登録

PostgreSQL ビューの管理

PostgreSQLBeginner
オンラインで実践に進む

はじめに

この実験では、PostgreSQL のビュー管理について探求します。主な目標は、単純なビューとマテリアライズドビューを含む、さまざまなタイプのビューを理解し、実装することです。

まず、employeesテーブルに基づいた単純なビューを定義することで、特定の列を選択するビューを作成する方法を示します。次に、ビューを介してデータのクエリと、場合によってはデータの変更方法を学びます。最後に、この実験では、マテリアライズドビューの作成とデータの挿入、およびこれらのビューを最新の状態に保つための手動更新について説明します。

単純なビューの定義

このステップでは、PostgreSQL で単純なビューを定義する方法を学びます。ビューは、SQL 文の結果セットに基づく仮想テーブルです。複雑なクエリを簡素化し、抽象化を提供し、データアクセスを制御するために役立ちます。

ビューの理解

ビューは本質的に保存されたクエリです。ビューをクエリすると、PostgreSQL は基になるクエリを実行し、あたかも実際のテーブルであるかのように結果セットを返します。ビュー自体はデータを格納しません。ビューは、基本テーブルに格納されたデータにアクセスするための別の方法を提供します。

employees テーブルの作成

まず、作業するためのemployeesテーブルを作成しましょう。ターミナルを開き、postgresユーザーとして PostgreSQL データベースに接続します。

sudo -u postgres psql

次に、employeesテーブルを作成します。

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

次に、employeesテーブルにサンプルデータを入力します。

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000.00),
('Jane', 'Smith', 'Marketing', 75000.00),
('Robert', 'Jones', 'Sales', 62000.00),
('Emily', 'Brown', 'IT', 80000.00),
('Michael', 'Davis', 'Marketing', 70000.00);

以下のクエリを実行して、データを確認できます。

SELECT * FROM employees;

出力に挿入されたデータが表示されるはずです。

employee_info ビューの定義

データを含むテーブルができたら、単純なビューを作成しましょう。このビューは、各従業員の氏名、姓、部署のみを表示します。以下の SQL 文を使用して、employee_infoという名前のビューを定義できます。

CREATE VIEW employee_info AS
SELECT first_name, last_name, department
FROM employees;

このステートメントは、employeesテーブルからfirst_name、last_name、department列を選択するemployee_infoという名前のビューを作成します。

ビューのクエリ

ビューをクエリするには、通常のテーブルと同様にSELECT文を使用できます。

SELECT * FROM employee_info;

このクエリは、ビューで定義されているように、すべての従業員の氏名、姓、部署を返します。

ビューの説明

psqlで\dコマンドを使用してビューの説明を表示できます。

\d employee_info

これにより、ビューの定義と含まれる列が表示されます。

PostgreSQL employee_info ビューの説明

ビューを使用したデータのクエリと変更

このステップでは、PostgreSQL のビューを介してデータをクエリおよび変更する方法を学びます。ビューは主にデータのクエリに使用されますが、場合によっては、基本テーブルにある基になるデータを変更するためにも使用できます。

ビューを介したデータのクエリ

前のステップで示したように、ビューを介したデータのクエリは簡単です。通常のテーブルであるかのように、SELECT文を使用してビューからデータを検索できます。

たとえば、employee_infoビューからすべてのデータを検索するには、次のとおりです。

SELECT * FROM employee_info;

また、WHERE句やその他の SQL 構文を使用して、データをフィルタリングおよびソートすることもできます。

SELECT * FROM employee_info WHERE department = 'Sales';

このクエリは、営業部門の従業員のみを返します。

ビューを介したデータの変更

ビューを介したデータの変更は、特定の条件下で可能です。ビューは、PostgreSQL がどの基本テーブルと列を更新するかを決定するために十分にシンプルでなければなりません。一般的に、ビューは次の条件を満たしている場合に修正可能です。

  • 1 つのテーブルからのみ選択されます。
  • 集約関数（例：SUM、AVG、COUNT）を含みません。
  • GROUP BY、HAVING、またはDISTINCT句を含みません。

より簡単に更新できるように、employee_idを含む別のビューを作成しましょう。

CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees;

次に、employee_detailsビューを介して従業員の給与を更新してみましょう。

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

このステートメントは、employee_idが 1 の従業員の給与を 65000.00 に更新します。

employeesテーブルを直接クエリして、更新を確認できます。

SELECT * FROM employees WHERE employee_id = 1;

employee_idが 1 の従業員の給与が更新されていることがわかるはずです。

ビューを介したデータの挿入

ビューを介してデータを挿入することもできます。ただし、ビューには基本テーブルのすべての NULL 以外の列が含まれている必要があります。employee_detailsビューにはemployeesテーブルのすべての列が含まれているため、新しい従業員を挿入できます。

INSERT INTO employee_details (first_name, last_name, department, salary)
VALUES ('David', 'Lee', 'IT', 90000.00);

employee_idを指定しないことに注意してください。これはシリアル列であり、自動的に生成されます。

挿入を確認します。

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

ビューを介したデータの削除

同様に、修正可能なビューを介してデータを削除できます。

DELETE FROM employee_details WHERE first_name = 'David' AND last_name = 'Lee';

削除を確認します。

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

重要な考慮事項

  • すべてのビューが修正可能とは限りません。結合、集約、またはその他の複雑な操作を含む複雑なビューは、通常、読み取り専用です。
  • ビューを介してデータを変更すると、パフォーマンスに影響を与える可能性があります。PostgreSQL は、ビュー操作を基になる基本テーブルの操作に変換する必要があります。
  • ビューを介してデータを変更する際には、変更が直接基本テーブルに影響を与えるため、注意が必要です。

PostgreSQL ビュー変更例

マテリアライズドビューの作成とデータの挿入

このステップでは、PostgreSQL で物理化ビューを作成および入力する方法を学びます。通常のビューとは異なり、物理化ビューはクエリ結果セットを物理的なテーブルとして格納します。これは、複雑なクエリやリモートソースからのデータにアクセスするクエリなど、クエリのパフォーマンスを大幅に向上させる可能性があります。ただし、物理化ビューのデータは、基になるデータが変更された場合に自動的に更新されません。手動で更新するか、定期的に更新されるようにスケジュールする必要があります。

物理化ビューの作成

物理化ビューを作成するには、CREATE MATERIALIZED VIEWステートメントを使用します。各部署の平均給与を示すemployee_salariesという名前の物理化ビューを作成しましょう。

CREATE MATERIALIZED VIEW employee_salaries AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

このステートメントは、employeesテーブルのデータに基づいて、各部署の平均給与を計算するemployee_salariesという名前の物理化ビューを作成します。

物理化ビューのクエリ

物理化ビューは、通常のテーブルと同様にクエリできます。

SELECT * FROM employee_salaries;

これにより、employeesテーブルに存在していたデータ（物理化ビューが作成された時点のデータ）に基づいて、各部署の部署名と平均給与が返されます。

物理化ビューの入力

物理化ビューを作成すると、初期データで自動的に入力されます。ただし、employeesテーブルの基になるデータが変更された場合、employee_salaries物理化ビューのデータは自動的に更新されません。

employeesテーブルに新しい従業員を挿入してみましょう。

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Johnson', 'IT', 85000.00);

これで、employee_salaries物理化ビューをもう一度クエリすると：

SELECT * FROM employee_salaries;

新しい従業員を反映するために、IT 部門の平均給与が変更されていないことに気付くでしょう。これは、物理化ビューが更新されていないためです。

物理化ビューの説明

psqlで\dコマンドを使用して物理化ビューの説明を表示できます。

\d employee_salaries

これにより、物理化ビューの定義と含まれる列が表示されます。

PostgreSQL 物理化ビューの説明

マテリアライズドビューを手動でリフレッシュする

このステップでは、PostgreSQL でマテリアライズド・ビューをマニュアルでリフレッシュする方法を学びます。前のステップで述べたように、マテリアライズド・ビューは基になるデータが変更されても自動的に更新されません。最新のデータを反映させるには、明示的にリフレッシュする必要があります。

マテリアライズド・ビューのリフレッシュ

マテリアライズド・ビューをリフレッシュするには、REFRESH MATERIALIZED VIEW ステートメントを使用します。主なオプションは 2 つあります。

  • REFRESH MATERIALIZED VIEW view_name: これは、マテリアライズド・ビューを定義するクエリを再実行してリフレッシュします。マテリアライズド・ビューに対して ACCESS EXCLUSIVE ロックを取得し、同時アクセスを防ぎます。

  • REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: これは、同時実行クエリをブロックせずにマテリアライズド・ビューをリフレッシュします。ただし、マテリアライズド・ビューに少なくとも 1 つのインデックスが必要です。

まず、標準の REFRESH MATERIALIZED VIEW コマンドを使用して employee_salaries マテリアライズド・ビューをリフレッシュしてみましょう。

REFRESH MATERIALIZED VIEW employee_salaries;

次に、employee_salaries マテリアライズド・ビューを再度クエリします。

SELECT * FROM employee_salaries;

IT 部門の平均給与が、新しい従業員を反映して更新されているはずです。

同時リフレッシュ

マテリアライズド・ビューを同時にリフレッシュするには、まずそれに UNIQUE インデックスを作成する必要があります。これは同時リフレッシュの要件です。PostgreSQL は、ビュー全体をロックせずにリフレッシュを実行するために、行を一意に識別する方法を必要とするためです。employee_salaries ビューの department 列は、ビューが部門ごとにグループ化されているため一意です。そのため、この列に一意インデックスを作成できます。

department 列に一意インデックスを作成しましょう。

CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);

これで、マテリアライズド・ビューを同時にリフレッシュできます。

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

データを最新の状態に保つために、employee_salaries マテリアライズド・ビューを再度クエリします。

SELECT * FROM employee_salaries;

適切なリフレッシュ方法の選択

  • 単純なマテリアライズド・ビューの場合、または短い期間の利用不可を許容できる場合は、REFRESH MATERIALIZED VIEW を使用します。
  • 大規模なマテリアライズド・ビューの場合、または同時実行クエリへの影響を最小限に抑えたい場合は、REFRESH MATERIALIZED VIEW CONCURRENTLY を使用します。最初にマテリアライズド・ビューにインデックスを作成することを忘れないでください。

重要な考慮事項

  • マテリアライズド・ビューのリフレッシュは、特に大規模なデータセットの場合、リソースを大量に消費する操作になる可能性があります。
  • cron のようなツールを使用して定期的なリフレッシュをスケジュールし、マテリアライズド・ビューのデータを最新の状態に保つことを検討してください。

psql シェルを終了するには、\q と入力して Enter キーを押してください。

まとめ

この実験では、PostgreSQL で単純なビューを定義する方法を学びました。まず、従業員 ID、氏名、部署、給与などの列を含むサンプルデータを持つemployeesテーブルを作成しました。次に、employeesテーブルから氏名、名字、部署のみを選択するemployee_infoビューを定義し、ビューがクエリを簡素化し、基になるデータの特定の視点を提供する方法を示しました。

また、ビューを介してデータのクエリと変更方法、および物理化ビューの作成と更新方法を学びました。物理化ビューは、クエリ結果をテーブルとして格納し、複雑なクエリのパフォーマンスを向上させます。同時更新を含む、物理化ビューを更新するさまざまな方法を探りました。

関連 PostgreSQL コース
初心者のための PostgreSQL

初心者のための PostgreSQL

databasepostgresql
高度な PostgreSQL 実践ラボ

高度な PostgreSQL 実践ラボ

databasepostgresql