PostgreSQLとは？特徴やインストール方法、SQLの例文まで紹介

2023年4月25日

この記事では、国内で使われている主要なRDBMSのうち「PostgreSQL」をテーマに、初級者から中級者向けに幅広い情報を提供します。

PostgreSQLの概要からWindows環境へのインストール方法、要件のほか、ユーザー権限や初歩的なSQL文の実行まで解説します。

また、PostgreSQL特有のエラーやその対処方法、拡張機能についても触れています。

当記事を読むことで、PostgreSQLへの理解が深まるでしょう。

PostgreSQLの概要

この章では、PostgreSQLの概要について説明します。まずは、PostgreSQLの特徴を理解することで、次章以降の具体的な活用に対する理解を深めることができます。

PostgreSQLはDBMSのひとつ

PostgreSQL（「ポストグレスキューエル」と発音）は、オープンソースのリレーショナルデータベース管理システム（RDBMS）です。

Linux、macOS、Windowsなどの複数のプラットフォームで利用可能であり、そのソースコードは完全に公開されています。また、用途に関わらず無料で利用できるため、非常に柔軟性が高いです。

さらに、postgreSQLのオープンソースライセンスは比較的緩やかであり、機能の改変や追加を行っても公開義務がありません。

PostgreSQLの歩み

PostgreSQLは、1986年にカリフォルニア大学バークレイ校の教授らによって、主に研究開発目的で「POSTGRES」という名前で開発が始まりました。

その後、1995年に改善が加えられ、新たに「Postgres95」として公開されました。1996年には、現在の「PostgreSQL」という名称に変更され、以降も開発と改良が続けられています。

Windows環境へのインストール

このセクションでは、PostgreSQLをWindows環境にインストールする際のハードディスク容量、メモリ容量、および動作環境について説明します。また、インストールの手順についても実際の画像を用いて説明します。

ハードディスク容量

PostgreSQLをインストールする際には、ハードディスク容量が十分にあることが必要です。

インストールに必要な最低容量は約350MBですが、実際のデータベースやインデックス、ログファイルなどを考慮すると、より大きな容量が必要になります。適切なハードディスク容量を確保することで、データベースのパフォーマンスや安定性を維持することができます。

メモリ容量

PostgreSQLのメモリ要件は、データベースのサイズやアクセスパターンによって異なります。

最低限のメモリ要件は1GBですが、実際には2GB以上のメモリが推奨されます。データベースのパフォーマンスを向上させるためには、適切なメモリ容量を確保することが重要です。

動作環境

PostgreSQLは、Windows 2000 SP4以降の環境で動作します。ただし、ファイルシステムについては、FATおよびFAT32は推奨されていません。これらのファイルシステムは、データベースのパフォーマンスや安定性に影響を与える可能性があるためです。

PostgreSQLインストール手順

当記事では、Windows11の環境でインストールを行います。

①PostgreSQLのインストーラーが提供されているサイトにアクセス

②最新のPOstgreSQLとお使いのPCのOSバージョンが一致するボタンをクリック

③当サイトの環境の場合は、Windows x86-64

④ダウンロード中の画面

⑤インストーラーがダウンロードできた

⑥インストーラーをダブルクリックして実行

⑦はいをクリック

⑧Nextで進む

⑨インストール先を設定してNextで進む
※画面はデフォルト

⑩Nextで進む
※左側はコンポーネント。基本はデフォルトのままでOK

⑪データの保存先を設定して、Nextで進む
※画面はデフォルト

⑫データベース管理者のパスワードを設定してNextで進む

⑬ポート番号を設定してNextで進む

他のアプリケーションがデフォルトの5432ポートを使用していなければ、デフォルトのままで進めて大丈夫です。

⑭ロケールを設定してNextで進む
※画面はデフォルト

⑮Nextで進む

⑯Nextで進む

⑰インストールが開始

⑱スタックビルダーはインストール後でも起動可能なため、当サイトではチェックを外してFinish

以上で、Windows環境へのPostgreSQLのインストールは完了です。

ユーザーの権限と作成方法

PostgreSQLでは、データベース全体に対する権限とデータやオブジェクト（テーブル）に対する権限の2種類があります。これらの権限を適切に割り当てることで、データベースのセキュリティや運用を効果的に管理できます。

データベース全体に対する権限

データベース全体に対する権限は、ユーザーがデータベース全体に対して実行できるアクションを制御します。主な権限には以下のようなものがあります。

権限	説明	付与するコマンド例
SUPERUSER	すべての権限を持つスーパーユーザー	`ALTER USER user_name WITH SUPERUSER;`
CREATEDB	データベースの作成ができる権限	`ALTER USER user_name WITH CREATEDB;`
CREATEROLE	ユーザー（ロール）の作成ができる権限	`ALTER USER user_name WITH CREATEROLE;`

テーブルに対する権限

テーブルに対する権限は、ユーザーがテーブルに対して実行できるアクションを制御します。主な権限には以下のようなものがあります（データやオブジェクトに対する権限も含みます）。

権限	説明	付与するコマンド例
SELECT	テーブルからデータを取得する権限	`GRANT SELECT ON TABLE table_name TO user_name;`
INSERT	テーブルにデータを挿入する権限	`GRANT INSERT ON TABLE table_name TO user_name;`
UPDATE	テーブルのデータを更新する権限	`GRANT UPDATE ON TABLE table_name TO user_name;`
DELETE	テーブルのデータを削除する権限	`GRANT DELETE ON TABLE table_name TO user_name;`
TRUNCATE	テーブルのデータを全削除する権限	`GRANT TRUNCATE ON TABLE table_name TO user_name;`
REFERENCES	外部キー制約の作成・変更ができる権限	`GRANT REFERENCES ON TABLE table_name TO user_name;`
TRIGGER	トリガーの作成・削除ができる権限	`GRANT TRIGGER ON TABLE table_name TO user_name;`

ユーザーの作成方法

ユーザーの作成は、CREATE USERまたはCREATE ROLEコマンドを使用します。ユーザー作成時にデータベース全体に対する権限とデータやオブジェクト（テーブル）に対する権限の両方を付与することができます。

ユーザー作成時に権限付与

たとえば、データベース作成権限とデータに対するSELECT権限を持つユーザーを作成するには、以下のようなコマンドを実行します。

CREATE USER new_user WITH CREATEDB;
GRANT SELECT ON TABLE example_table TO new_user;

上記のコマンドでは、まずCREATE USERで新しいユーザー（new_user）を作成し、データベース作成権限（CREATEDB）を付与しています。次に、GRANTコマンドを使用して、既存のテーブル（example_table）に対するSELECT権限を新しいユーザー（new_user）に付与しています。

複数の権限を付与

同様に、他の権限もGRANTコマンドを使用して付与することができます。複数の権限を一度に付与する場合は、カンマで区切って指定します。たとえば、INSERTとUPDATE権限を同時に付与する場合は以下のように実行します。

GRANT INSERT, UPDATE ON TABLE example_table TO new_user;

権限を削除

ユーザーの権限を削除する場合は、REVOKEコマンドを使用します。たとえば、先ほど付与したSELECT権限を削除するには、以下のように実行します。

REVOKE SELECT ON TABLE example_table FROM new_user;

ユーザーの作成と権限の付与により、データベースの運用を効果的に管理できます。適切な権限設定を行うことで、セキュリティを強化し、データベースを安全に運営することができます。

初歩的なSQLをPostgreSQLで実行する

この章では、PostgreSQLで初歩的なSQLを実行する方法について説明します。psqlの使い方から、テーブルの作成、データの挿入、データの検索について解説します。

psqlについて

psqlは、PostgreSQLのコマンドラインインターフェースで、データベースの管理やSQLの実行が可能です。Windows、macOS、Linuxのいずれの環境でも利用できます。psqlを使用することで、対話的にSQLコマンドを実行したり、SQLファイルを実行することができます。

分かりやすくいうと「コンソール」

psqlについて: psqlは、PostgreSQLのコマンドラインインターフェースです。

psqlを使って、データベースに対して操作やクエリを実行することができます。初心者の方は、psqlを「コンソール」と理解してもらえばよいでしょう。

テキストベースの操作でデータベースにアクセスし、SQL文を実行することが可能です。

CREATE TABLE：テーブルの作成

テーブルの作成にはCREATE TABLE文を使用します。次の例では、”employees”という名前のテーブルを作成しています。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INTEGER,
    department VARCHAR(255)
);

この例では、id、name、age、departmentという4つのカラムが定義されています。idは主キーとして設定されており、nameはNULLを許容しないことが指定されています。

INSERT：データの挿入

データをテーブルに挿入するにはINSERT文を使用します。次の例では、”employees”テーブルに1行のデータを挿入しています。

INSERT INTO employees (name, age, department)
VALUES ('Taro Yamada', 28, 'Sales');

この例では、nameに「Taro Yamada」、ageに28、departmentに「Sales」というデータを挿入しています。

SELECT：データの検索

データをテーブルから検索するにはSELECT文を使用します。次の例では、”employees”テーブルから全てのデータを検索しています。

SELECT * FROM employees;

もし特定の条件に一致するデータを検索したい場合は、WHERE句を使用します。次の例では、ageが30以上の従業員を検索しています。

SELECT * FROM employees WHERE age >= 30;

PostgreSQL固有のエラーメッセージと対処方法

PostgreSQLでは、いくつかの固有のエラーメッセージが出力されることがあります。ここでは、よく出会うエラーメッセージとその対処方法をいくつか紹介します。

リレーション””の列””のNULL値が非NULL制約に違反しています

このエラーメッセージは、NOT NULL制約が設定されているカラムに対して、NULL値を挿入しようとしたときに発生します。対処方法は以下の通りです。

INSERT文やUPDATE文で、NOT NULL制約が設定されているカラムに適切な値を設定してください。
NULL値を許容するように、テーブル定義を変更することも検討できますが、データ整合性を確保するために、適切なデフォルト値を設定することが望ましいです。

テーブル””への挿入、更新は外部キー制約””に違反しています

このエラーは、外部キー制約に違反したときに発生します。具体的には、親テーブルに存在しない値を子テーブルの外部キーに設定しようとした場合などです。対処方法は以下の通りです。

親テーブルに存在する適切な外部キー値を子テーブルに設定してください。
外部キー制約が不要な場合は、テーブル定義を変更して外部キー制約を削除することもできますが、データ整合性の確保に注意してください。

“”型の入力構文が不正です: “”

このエラーは、データ型に対して無効な値が入力されたときに発生します。例えば、数値型のカラムに文字列を挿入しようとした場合などです。対処方法は以下の通りです。

入力値がカラムのデータ型に適合するように、INSERT文やUPDATE文を修正してください。
必要に応じて、テーブル定義を変更してカラムのデータ型を変更することも検討できます。

値は型character varying(__)としては長すぎます

このエラーは、文字列型のカラムに対して、定義された最大長を超える文字列を挿入しようとしたときに発生します。対処方法は以下の通りです。

INSERT文やUPDATE文で、カラムの最大長以下の文字列を設定してください。
必要に応じて、テーブル定義を変更してカラムの最大長を拡張することも検討できます。

これらのエラーメッセージは、PostgreSQLでデータ操作を行う際によく遭遇するものです。適切な対処方法を理解し、データ整合性を確保しながらデータベースを運用することが大切です。エラーメッセージが発生した際は、メッセージの内容をよく読み、原因と対処方法を理解しましょう。

PostgreSQLの設定や運用に関する注意点

この章では、PostgreSQLの設定や運用に関する注意点を解説します。RDBMSを運用するうえで発生するロック管理やデッドロックの対処、パフォーマンス問題の解決策、設定や運用に関連するトラブルシューティングについて取り上げます。

それぞれ簡単な説明にとどめていますが、詳細は後日、別記事で説明する予定です。

PostgreSQLでのロック管理とデッドロックの対処

PostgreSQLでは、同時に複数のトランザクションが実行される際、データの整合性を保つためにロックが行われます。ただし、ロックの競合が発生するとデッドロックが起こる可能性があります。デッドロックを検出した場合、PostgreSQLは自動的にいずれかのトランザクションを中断し、エラーメッセージを表示します。

デッドロックの対処方法としては、次のような方法があります。

ロックの競合が発生しないように、トランザクションの実行順序を調整する。
ロックタイムアウトの設定を調整し、デッドロックの検出を早める。
リトライロジックを実装し、トランザクションが失敗した場合に再試行する。

PostgreSQLのパフォーマンス問題の解決策

PostgreSQLのパフォーマンス問題は、さまざまな要因によって引き起こされることがあります。主な解決策は以下の通りです。

インデックスの適切な利用: 適切なインデックスを設定することで、クエリのパフォーマンスを向上させることができます。
クエリの最適化: クエリを見直し、不要なJOINやサブクエリを削除することで、パフォーマンスを改善することができます。
ハードウェアのアップグレード: ディスク容量やメモリ、CPUの性能を向上させることで、パフォーマンスを改善することができます。
設定の最適化: PostgreSQLの設定を見直し、適切なパラメータに調整することで、パフォーマンスを改善することができます。

PostgreSQLの設定や運用に関連するトラブルシューティング

ここではPostgreSQLで発生する一般的な問題やその解決方法について説明します。

設定ファイルの誤編集: 設定ファイル（postgresql.conf）を編集する際に誤った設定を行うと、PostgreSQLが正常に動作しなくなることがあります。誤編集があった場合、適切な設定に戻すことで問題を解決できます。
ログの確認: PostgreSQLのログには、エラーや警告が記録されています。問題が発生した場合、ログを確認することで原因を特定し、解決策を見つけることができます。
バックアップとリストア: データの損失や障害が発生した場合、定期的なバックアップを取っておくことで、データをリストアし、システムの復旧が可能となります。
バージョンアップ: PostgreSQLの新しいバージョンでは、パフォーマンスの向上やセキュリティの強化が行われています。定期的にバージョンアップを行うことで、新しい機能や改善版を利用できます。

これらのトラブルシューティング方法を適切に適用することで、PostgreSQLの設定や運用に関連する問題を解決することができます。

PostgreSQLの拡張機能

この章では、PostgreSQLの拡張機能について説明します。代表的な拡張機能の簡単な説明にとどめていますが、詳細は後日、別記事で説明する予定です。

拡張機能とは

PostgreSQLの拡張機能は、データベースシステムの機能を追加・拡張するためのモジュールです。これらの拡張機能を利用することで、さまざまな用途に合わせたカスタマイズが可能となります。以下に、いくつかの代表的な拡張機能を紹介します。

PostGIS: 地理情報システム (GIS) 機能

PostGISは、PostgreSQLで地理情報システム（GIS）を利用するための拡張機能です。地理空間データを格納、管理、操作する機能を提供し、地理情報を活用したアプリケーション開発に役立ちます。

pgcrypto: 暗号化・ハッシュ関数や乱数生成機能

pgcryptoは、PostgreSQLで暗号化、ハッシュ関数、乱数生成などのセキュリティ関連機能を提供する拡張機能です。データの安全性やプライバシーを確保するために重要な機能を追加することができます。

hstore: キー・バリューストア機能

hstoreは、PostgreSQLにキー・バリューストア機能を追加する拡張機能です。これにより、データをキーと値のペアとして保存・検索することができます。NoSQLデータベースのような機能をPostgreSQL内で実現することができます。

Full Text Search: フルテキスト検索機能

PostgreSQLのフルテキスト検索機能は、テキストデータの検索性能を向上させるための機能です。この拡張機能を利用することで、大量のテキストデータから高速かつ効率的に特定の単語やフレーズを検索できます。

これらの拡張機能は、PostgreSQLの柔軟性と拡張性を示す良い例です。必要に応じて機能を追加することで、PostgreSQLをより効果的に活用することができます。

あとがき

本記事では、PostgreSQLの基本的な概要からインストール方法、権限の設定、SQLの実行、エラーメッセージと対処方法、そして拡張機能まで幅広く解説しました。PostgreSQLはオープンソースのリレーショナルデータベース管理システムとして、その柔軟性、拡張性、パフォーマンスが高く評価されています。

本記事を通じて、PostgreSQLの基本的な操作や機能を理解し、実際に利用することでデータ管理やアプリケーション開発に活用することを願っています。また、エラーメッセージや拡張機能についての情報も、実践的な運用時のトラブルシューティングや機能追加の参考になれば幸いです。

データベース技術は日々進化しており、PostgreSQLもその一環として機能追加や改善が行われています。最新の情報を常にチェックし、知識をアップデートしていくことが重要です。

当サイトでは今後も、PostgreSQLを活用したデータ管理やアプリケーション開発に関する情報を発信していきます。

PostgreSQLを活用したデータ管理やアプリケーション開発にに関する情報を発信していきます。