【PostgreSQL】psqlでデータベースを操作する

【PostgreSQL】psqlでのデータベース操作

PostgreSQLのCUIツールであるpsqlは、データベースを効率的に操作するための非常に強力な手段です。本記事ではCUI初心者やPostgreSQLの学習を始めたばかりの方を対象に、psqlの基本的な使い方や具体的なコマンド例を紹介します。

psqlとは

psqlは、PostgreSQLのCUI（文字ベースのユーザーインターフェース）ツールで、SQLコマンドを実行してデータベースを管理したり操作したりするために使用されます。軽量で高速、スクリプト化が可能という利点があり、PostgreSQLを効率よく操作するのに欠かせないツールです。

SQLコマンドの分類

SQLはRDBMSに対する標準の「問い合わせ言語」です。標準を完全に準拠しているRDBMSはありませんがPostgreSQLは下記の主要な部分に準拠しています。

  • DDL（Data Definition Language）
    データベースのテーブル等のオブジェクトを定義・変更します。
    • 主なコマンド
      • CREATE
      • ALTER
      • DROP
  • DML（Data Manipulation Language）
    データベース内のデータを操作します。データの挿入/追加/削除等を行います。
    • 主なコマンド
      • SELECT
      • INSERT
      • UPDATE
      • DELETE
  • DCL（Data Control Language）
    データベースへのアクセス権限を制御します。トランザクション関連のコマンドも含まれます。

SQLコマンドの詳細は下記ブログを参照ください。
【参考ブログ】：データベースカテゴリ「PosygreSQL」

GUIとCUIの比較

PostgreSQLを操作するには、GUI（Graphical User Interface）とCUI（Character User Interface）の2つの方法があります。それぞれのメリットとデメリットを確認してみましょう。

GUI（pgAdmin4等）

  • メリット
    • 視覚的にデータを確認しやすい
    • ボタン操作で機能を利用できるため初心者でも扱いやすい
    • ER図やスキーマデザインツールなど、視覚化ツールが豊富
  • デメリット
    • 動作が重くなることがある
    • 複雑な操作では効率が悪い場合がある
    • CLIツール1に比べてスクリプトの自動化が難しい

CUI（例: psql等）

  • メリット
    • 軽量で高速に操作可能
    • シェルスクリプトと組み合わせて操作を自動化できる
    • ネットワーク経由でリモートのPostgreSQLに直接アクセス可能
  • デメリット
    • 初心者にとってはコマンドを覚える必要がある
    • データの視覚化ができない
    • GUIと比較して操作ミスが生じやすい

psqlの起動方法

代表的な２つの方法を紹介します。

スタートメニューからの起動

SQL Shell(psql)をクリックするとコマンドプロンプトが起動し、
①Server
②Database名
③Port番号
④Username
⑤Client Encording [SJIS]
を聞いてくるので適宜入力します。本例ではデータベース名以外はEnterを押下してでデフォルトを使用しています。接続が確立すると下図のように、接続プロンプト「sales_db=#」が表示されます。プロンプトの「=#」はスーパーユーザーで接続している事を示し、「=>」は一般ユーザーを示します。

ユーザー名とデータベース名を指定して起動

コマンドプロンプトから下図のように入力して接続します。このとき、ユーザー名にログイン属性が付与されていないとエラーメッセージが表示されて接続できません。
コマンド例）：「psql -U ユーザー名 -d データベース名」　又は、「psql ユーザー名 データベース名」

psqlの基本コマンド

psqlコマンドオプション

psqlコマンドには様々なオプションを指定できます。ここでは、利用可能なオプションのいくつかを紹介します。

  • -l
    データベースの一覧を表示する
  • -C ’コマンド’
    引数で指定したコマンドを実行し結果を表示する
  • -f ファイ名
    引数で指定したファイルの内容をSQLとして実行する
  • -h ホスト名
    接続するPostgreSQLのホスト名を指定する

データベース接続後のバックスラッシュコマンド

データベース内のテーブルやインデックス等のオブジェクトを参照できるコマンドです。以下に例をし示します。

  • \d
    テーブル/インデックス/シーケンス/ビューの一覧を表示する

■バックスラッシュコマンド一覧

№コマンド機　　　　　　　能
１\cデータベースの接続を変更する。
２\dテーブル、ビュー、シーケンス、インデックスなどの詳細を表示する。
３\dtテーブルの一覧を表示する。
４\dvビューの一覧を表示する。
５\diインデックスの一覧を表示する。
６\dsシーケンスの一覧を表示する。
７\df関数の一覧を表示する。
８\dn名前空間（スキーマ）の一覧を表示する。
９\l or \listデータベースの一覧を表示する。
10\iファイルからSQLコマンドを読み込んで実行する。
11\g現在のクエリを実行する。
12\qpsqlを終了する。
13\?psqlのヘルプを表示する。
14\timingコマンドの実行時間を表示する。
15\x拡張表示モードの切り替え。

psqlでSQLコマンドを実行する

先ずデータベースへ接続しテーブルの一覧を表示します。

C:\>psql sales_db postgres
psql (16.4)
"help"でヘルプを表示します。

sales_db=> \dt
              リレーション一覧
 スキーマ |    名前    |  タイプ  |  所有者
----------+------------+----------+----------
 public   | books      | テーブル | postgres
 public   | bookstock  | テーブル | postgres
 public   | customers  | テーブル | postgres
 public   | customers2 | テーブル | postgres
 public   | customers3 | テーブル | postgres
 public   | sales      | テーブル | postgres
(6 行)

sales_db=>

booksテーブルの構造と内容を参照してみます。3件のレコードが格納されているのが分かります。

sales_db=> \d books
                                            テーブル"public.books"
     列     |           タイプ            | 照合順序 | Null 値を許容 |               デフォルト
------------+-----------------------------+----------+---------------+----------------------------------------
 book_id    | integer                     |          | not null      | nextval('books_book_id_seq'::regclass)
 title      | character varying(150)      |          | not null      |
 author     | character varying(100)      |          |               |
 price      | numeric(10,2)               |          | not null      |
 stock      | integer                     |          |               | 0
 created_at | timestamp without time zone |          |               | now()
インデックス:
    "books_pkey" PRIMARY KEY, btree (book_id)


sales_db=> SELECT * FROM books;
 book_id |       title       |  author   |  price  | stock |         created_at
---------+-------------------+-----------+---------+-------+----------------------------
       1 | PostgreSQL入門    | 鈴木 一郎 | 3000.00 |     2 | 2024-12-06 20:28:10.549144
       3 | A5:SQL Mk-2の基礎 | 松原 正和 | 2800.00 |    10 | 2024-12-06 20:28:10.549144
       6 | Python入門        | 田中 正   | 2500.00 |     5 | 2024-12-07 12:34:40.646229
(3 行)

sales_db=>

booksテーブルに１件のレコードを追加します。「books_id 9 psqlの基礎」が追加されました。

sales_db=# INSERT INTO public.books (
sales_db(# title, author, price, stock) VALUES (
sales_db(# 'psqlの基礎'::character varying, '山田　正'::character varying, '2600'::numeric, '5'::integer)
sales_db-#  returning book_id;
 book_id
---------
       9
(1 行)


INSERT 0 1
sales_db=# SELECT * FROM books;
 book_id |       title       |  author   |  price  | stock |         created_at
---------+-------------------+-----------+---------+-------+----------------------------
       1 | PostgreSQL入門    | 鈴木 一郎 | 3000.00 |     2 | 2024-12-06 20:28:10.549144
       3 | A5:SQL Mk-2の基礎 | 松原 正和 | 2800.00 |    10 | 2024-12-06 20:28:10.549144
       6 | Python入門        | 田中 正   | 2500.00 |     5 | 2024-12-07 12:34:40.646229
       9 | psqlの基礎        | 山田　正  | 2600.00 |     5 | 2024-12-28 14:29:00.135625
(4 行)


sales_db=#

以上のように、psqlでバックスラッシュコマンドやSQLコマンドを使用できれば、GUIに比べ短時間で操作ができるようになります。

  1. CLI：（Command Line Interface ツール）は、コマンドラインインターフェイス上で動作するソフトウェアやツールのことを指します。 ↩︎

まとめ

各種コマンドを覚えてしまえば、psqlを使用した方が作業効率は格段に向上します。しかし誤りやすい面もあるので慎重に使用する必要があります。
頻繁にPostgreSQLを使用するならば、簡単なコマンドを徐々に覚えてpsqlを使用してみてはどうでしょうか？　業務効率は格段に向上していく筈です。

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