【PostgreSQL】psqlコマンドの基本|実例付きで徹底解説

※本サイトにはプロモーション・広告が含まれています。

(最終更新日:2023年8月)

✔このような方へ向けて書かれた記事となります

「PostgreSQL コマンドってどんな機能があるの?」
「PostgreSQL コマンドの使い方を学びたい」
「PostgreSQL コマンドによる実践例が知りたい」

✔当記事を通じてお伝えすること

  • PostgreSQL コマンドの基礎知識
  • PostgreSQL コマンドの書き方や応用方法
  • PostgreSQL コマンドを用いた具体例

当記事では、PostgreSQL コマンドの基本から応用まで、実例を交えてわかりやすく解説していきます。

ぜひ最後までお読みください。

筆者プロフィール

筆者プロフィールアイコン

【現職】プロジェクトマネージャー/SE(元外資系金融機関営業)

【副業】ブログ(月間15万PV)/YouTube/Web・アプリ制作

コミュニケーション × ビジネススキル × 文章力 × プログラミングスキルを活かし、30後半からのIT系職へシフト。当サイトでは、実際に手を動かせるWebアプリの開発を通じて、プログラミングはもちろん、IT職に必要な情報を提供していきます。

【当ブログで紹介しているサイト】

当サイトチュートリアルで作成したデモ版日報アプリ

Django × Reactで開発したツール系Webアプリ

✔人に見せても恥ずかしくないコードを書こう

「リーダブルコード」は、わかりやすく良いコードの定義を教えてくれる本です。

  • 見るからにきれいなコードの書き方
  • コードの分割方法
  • 変数や関数の命名規則

エンジニアのスタンダートとすべき基準を一から解説しています。

何回も読むのに値する本なので、ぜひ手にとって読んでみてください。

PostgreSQLとは

PostgreSQLは、オープンソースのリレーショナルデータベース管理システム(RDBMS)のひとつ。

SQL(Structured Query Language)という言語を使用してデータの格納から取得、操作をおこなえるデータベースです。

その強力な特性や高い拡張性、オープンソースライセンスにより、大規模な企業からスタートアップまで幅広く利用されています。

PostgreSQLのインストールと基本的な設定

こちらでは、PostgreSQLのインストール方法と、基本的な設定についてご説明します。

  • インストール方法
  • 設定ファイルの変更
  • サービスの操作

インストール方法

PostgreSQLは多くのプラットフォームで利用可能です。

WindowsやMac、Linuxなど、各OSごとの公式インストールガイドに従ってインストールをおこないます。

例えば、Ubuntuの場合、以下のコマンドを実行してPostgreSQLをインストールが可能です。

sudo apt update
sudo apt install postgresql postgresql-contrib

設定ファイルの変更

PostgreSQLの設定は、主にpostgresql.confという設定ファイルでおこないます。

このファイルには、以下が含まれています。

  • データベースのポート番号
  • メモリの設定
  • ログの設定

必要に応じてこの設定ファイルを編集し、PostgreSQLサービスを再起動することで設定を反映させます。

PostgreSQLのデフォルトポート番号は、「5432」。

覚えておくと便利です。

サービスの操作

PostgreSQLは、OSのサービスとして動作します。

以下の操作は、OSのシステムコマンドを使っておこなってください。

  • サービスの開始
  • 停止
  • 再起動

たとえば、Ubuntuでは以下のように行います。

sudo service postgresql start
sudo service postgresql stop
sudo service postgresql restart

PostgreSQLコマンドの基本

こちらでは、PostgreSQLのコマンドラインツールであるpsqlについて説明します。

  • psqlコマンド紹介
  • psqlへの接続
  • 入力ルールとショートカット

psqlコマンド紹介

psqlは、以下をおこなうためのコマンドラインインターフェイスです。

  • PostgreSQLサーバーへの接続
  • SQLクエリの実行

コマンドラインから直接クエリを実行したり、SQLスクリプトを実行したりできます。

データベースのメタデータの表示やSQLクエリの結果の表示フォーマットのカスタマイズなど、多彩な機能を提供しています。

psqlへの接続

psqlへの接続は、コマンドラインから次のようにおこないます。

psql -h localhost -U postgres

各オプションは以下の意味を持ちます。

  • -hオプション:ホスト名(この場合はlocalhost)
  • -Uオプション:ユーザ名(この場合はpostgres)

パスワードは、プロンプトが表示された後で入力しましょう。

入力ルールとショートカット

psqlでSQLを入力する際の基本的なルールは、一般的なSQLの構文ルールと同様です。

ただし、psqlには便利なショートカットがいくつかあります。

  • \l:データベースの一覧を表示
  • \dt:現在接続しているデータベースのテーブル一覧を表示
  • \q:psqlからのログアウト(終了)

データベース操作

こちらでは、PostgreSQLでデータベースを操作する方法について説明します。

  • 作成と削除
  • 一覧の表示
  • 接続と切断

作成と削除

データベースの作成は、CREATE DATABASEコマンドを使っておこないます。

testdbという名前のデータベースを作成するには、次のコマンドです。

CREATE DATABASE testdb;

データベースの削除は、DROP DATABASEコマンドを使用します。

testdbデータベースを削除するには次のとおりです。

DROP DATABASE testdb;

一覧の表示

psql内で利用可能な全てのデータベースの一覧を表示するには、psqlのショートカットコマンド \l を使用します。

\l

接続と切断

特定のデータベースに接続するには、\cまたは\connect コマンドにデータベース名を指定します。

例えば、testdbに接続するには以下のようにします。

\c testdb

接続を終了するには、\qまたは\quitを使用します。

テーブル操作

こちらでは、PostgreSQLでテーブルを操作する方法について詳しく説明します。

具体的なトピックは以下の通りです。

  • 作成と削除
  • 構造の確認
  • データのCRUD
  • データのフィルタリング

作成と削除

テーブルの作成は、CREATE TABLEコマンドを使用します。

例えば、名前と年齢を格納するpersonsテーブルを作成するには、以下のように実行します。

CREATE TABLE persons (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

テーブルを削除するには、DROP TABLEコマンドを使用します。

personsテーブルを削除するには以下のように実行します。

DROP TABLE persons;

構造の確認

テーブルの構造(スキーマ)を確認するには、\dコマンドを使用します。

例えば、personsテーブルのスキーマを確認するには以下のように実行してください。

\d persons

データのCRUD

テーブルへのCRUD操作は、それぞれのSQLコマンドを使用してください。

  • データの追加(Insert)
  • 読み出し(Select)
  • 更新(Update)
  • 削除(Delete)

例えば、personsテーブルにデータを追加するには、次のようなINSERTコマンドを使用します。

INSERT INTO persons (name, age) VALUES ('John Doe', 30);

データのフィルタリング

データの読み出し時に特定の条件を満たすデータだけを抽出するには、SELECT文のWHERE句を使用します。

例えば、personsテーブルから年齢が30以上の人物のみを抽出するには、次のようにします。

SELECT * FROM persons WHERE age >= 30;

関数とインデックス

こちらでは、PostgreSQLの関数とインデックスについて詳しく説明します。

  • 関数の利用
  • インデックスの作成と削除

関数の利用

PostgreSQLでは、データの操作や加工を行うための多くの関数が用意されています。

  • 文字列の長さを取得する:LENGTH関数
  • 文字列を大文字に変換する:UPPER関数

これらの関数は、SELECT文などの中で直接使用可能です。

SELECT name, LENGTH(name) as length FROM persons;

インデックスの作成と削除

インデックスは、データベースのパフォーマンスを向上させるための重要な機能です。

インデックスを作成するには、CREATE INDEXコマンドを使用します。

personsテーブルのnameカラムにインデックスを作成するには、次のとおりです。

CREATE INDEX idx_persons_name ON persons (name);

インデックスを削除するには、DROP INDEXコマンドを使用します。

DROP INDEX idx_persons_name;

テーブル構造の変更

こちらでは、既存のテーブル構造の変更方法について詳しく説明します。

  • カラムの追加・変更・削除
  • 制約の追加・変更・削除

カラムの追加・変更・削除

既存のテーブルに新しいカラムを追加するには、ALTER TABLEコマンドとADD COLUMN句を使用します。

例えば、personsテーブルにemailカラムを追加するには、次のとおり。

ALTER TABLE persons ADD COLUMN email VARCHAR(100);

カラムのデータ型を変更するには、ALTER TABLEコマンドとALTER COLUMN句を使用します。

ALTER TABLE persons ALTER COLUMN email TYPE TEXT;

カラムの削除には、ALTER TABLEコマンドとDROP COLUMN句を使用しましょう。

ALTER TABLE persons DROP COLUMN email;

制約の追加・変更・削除

テーブルのカラムには、データ整合性を保つための制約を追加できます。

以下は、personsテーブルのemailカラムに一意性制約を追加する例です。

ALTER TABLE persons ADD CONSTRAINT email_unique UNIQUE(email);

制約を削除するには、ALTER TABLEコマンドとDROP CONSTRAINT句を使用します。

ALTER TABLE persons DROP CONSTRAINT email_unique;

ビューの作成と利用

こちらでは、PostgreSQLでビューを作成し利用する方法について詳しく説明します。

  • ビューの作成
  • ビューの更新と削除
  • ビューの活用例

ビューの作成

ビューは、ひとつ以上のテーブルから取得した結果をまとめたもの。

仮想的なテーブルとして扱えます。

ビューの作成には、CREATE VIEWコマンドを使用してください。

例えば、personsテーブルから名前と年齢だけを取り出したビューを作成するには、次のようにします。

CREATE VIEW view_persons AS
SELECT name, age FROM persons;

ビューの更新と削除

ビューの内容を更新するには、CREATE OR REPLACE VIEWコマンドを使用します。

ビューの削除には、DROP VIEWコマンドを使いましょう。

DROP VIEW view_persons;

ビューの活用例

ビューは、複雑なクエリを簡単に再利用するためや、特定のユーザに対するデータの見え方を制御するためなどに活用できます。

以下は、年齢が30以上の人物の名前だけを取得するビューを作成する例です。

CREATE VIEW view_persons_over30 AS
SELECT name FROM persons WHERE age >= 30;

SELECT * FROM view_persons_over30;

ユーザ管理

こちらでは、PostgreSQLでのユーザ管理について詳しく説明します。

  • ユーザの作成・削除
  • 一覧の表示
  • 権限設定

ユーザの作成・削除

新しいユーザを作成するには、CREATE USERコマンドを使用します。

ユーザの削除には、DROP USERコマンドを使用してください。

CREATE USER testuser WITH PASSWORD 'testpass';
DROP USER testuser;

一覧の表示

システムに存在する全てのユーザの一覧を表示するには、\duコマンドを使用します。

\du

権限設定

特定のユーザに対するテーブルやデータベースのアクセス権限を設定することができます。

これは、GRANTおよびREVOKEコマンドを使用して行います。

GRANT SELECT, INSERT, UPDATE, DELETE ON persons TO testuser;
REVOKE SELECT, INSERT, UPDATE, DELETE ON persons FROM testuser;

外部ファイルのインポート・エクスポート

こちらでは、PostgreSQLでの外部ファイルのインポートおよびエクスポートについて詳しく説明します。

具体的な話題は以下の通りです。

  • データのインポート
  • データのエクスポート

データのインポート

CSVファイルなどの外部ファイルからデータをインポートする場合、COPYコマンドを使用。

以下の例では、persons.csvファイルからデータを読み込み、personsテーブルに挿入しています。

COPY persons FROM '/path/to/persons.csv' CSV HEADER;

データのエクスポート

逆に、テーブルのデータを外部ファイルにエクスポートする場合もCOPYコマンドを使用。

以下の例では、personsテーブルのデータをpersons_export.csvファイルにエクスポートしています。

COPY persons TO '/path/to/persons_export.csv' CSV HEADER;

まとめ

当記事では以下の内容をご説明しました。

PostgreSQLの基本的なインストールから設定

  • コマンドの使い方
  • テーブル操作
  • ビューの作成
  • ユーザ管理
  • 外部ファイルのインポート・エクスポート

身につけるには、実際に手を動かしていろいろ試すことが大切です。

自身の環境だけでなく、Dockerなどのコンテナであれば失敗しても問題ありません。

以下の記事などを参考に、たくさん触る機会を作りましょう。

タイトルとURLをコピーしました